Friday 29 March 2013

To find the weekend

/* Formatted on 3/29/2013 4:16:11 PM (QP5 v5.114.809.3010) */
SELECT   ROUND( (NEXT_DAY (TO_DATE (:binddate, 'DD-MON-YYYY') - 1,
                           'SATURDAY')
                 - NEXT_DAY (
                      TO_DATE (
                         '01-JAN-'
                         || EXTRACT (
                               YEAR FROM TO_DATE (:binddate, 'DD-MON-YYYY')
                            ),
                         'DD-MON-YYYY'
                      )
                      - 7,
                      'SUNDAY'
                   ))
               / 7)
            week_number
  FROM   DUAL




/* Formatted on 3/29/2013 4:16:19 PM (QP5 v5.114.809.3010) */
SELECT   NEXT_DAY (TO_DATE (:binddate, 'DD-MON-YYYY') - 1, 'SATURDAY')
  FROM   DUAL





/* Formatted on 3/29/2013 4:16:25 PM (QP5 v5.114.809.3010) */
SELECT   NEXT_DAY (TO_DATE (:binddate, 'DD-MON-YYYY') - 1, 'SATURDAY')
         - NEXT_DAY (
              TO_DATE (
                 '01-JAN-'
                 || EXTRACT (YEAR FROM TO_DATE (:binddate, 'DD-MON-YYYY')),
                 'DD-MON-YYYY'
              )
              - 7,
              'SUNDAY'
           )
  FROM   DUAL

To Avoide The Special Character chr(13) and chr(10)

/* Formatted on 3/29/2013 4:09:14 PM (QP5 v5.114.809.3010) */
SELECT   'a' || CHR (10) || 'b' FROM DUAL;

/* Formatted on 3/29/2013 4:09:11 PM (QP5 v5.114.809.3010) */
SELECT      'The total count is:'
         || CHR (10)
         || 'Registration: 1111'
         || CHR (10)
         || 'Fees paid: 2222'
         || CHR (10)
         || 'Admission Done: 3333'
         || CHR (10)
         || 'Total:4000'
  FROM   DUAL


You can use REGEXP_REPLACE to add a newline after every 3 characters
:-

/* Formatted on 3/29/2013 4:09:04 PM (QP5 v5.114.809.3010) */
SELECT   loc, REGEXP_REPLACE (loc, '(.{3})', '\1' || CHR (10)) AS loc3
  FROM   scott.dept



/* Formatted on 3/29/2013 4:08:54 PM (QP5 v5.114.809.3010) */
DECLARE
   v_desc_en_4k VARCHAR2 (1000)
         :=    CHR (10)
            || 'A = Add account'
            || CHR (13)
            || CHR (10)
            || 'C = Change account'
            || CHR (13)
            || CHR (10)
            || 'D = Delete account'
            || CHR (13)
            || CHR (10) ;
BEGIN
   DBMS_OUTPUT.put_line ('----before trim----');
   DBMS_OUTPUT.put_line (v_desc_en_4k);
   DBMS_OUTPUT.put_line ('-------------');
   DBMS_OUTPUT.put_line ('-----after trim----');
   v_desc_en_4k := TRIM (CHR ( 13 ) FROM TRIM (CHR ( 10 ) FROM v_desc_en_4k));
   DBMS_OUTPUT.put_line (v_desc_en_4k);
   DBMS_OUTPUT.put_line ('-------------');
END;

Tuesday 26 March 2013

To FInd the Undefine Customer in Receipt (OR) Null receipt customer in AR

 /* Formatted on 3/27/2013 1:13:18 PM (QP5 v5.114.809.3010) */
SELECT   DISTINCT gcc.segment1 Company,
                  bat.NAME batch_name,
                  arc.deposit_date deposit_date,
                  SUBSTRB (party.party_name, 1, 50) customer_name,
                  cust.ACCOUNT_NUMBER customer_number,
                  arc.receipt_number receipt_number,
                  arc.receipt_date receipt_date,
                  ara.status receipt_status,
                  ara.amount_applied receipt_amount,
                  gcc.concatenated_segments applied_gl_account,
                  ara.gl_date gl_date,
                  ara.apply_date applied_date,
                  trx.trx_number applied_trx_number,
                  trx.trx_date applied_trx_date
  FROM   ar_receivable_applications_all ara,
         ar_cash_receipts_all arc,
         ar_receipt_methods arm,
         hz_parties party,
         hz_cust_accounts cust,
         gl_code_combinations_kfv gcc,
         ra_customer_trx_all trx,
         ar_cash_receipt_history_all acrh,
         ar_batches_all bat,
         hr_operating_units hou
 WHERE       1 = 1
         AND arc.cash_receipt_id = ara.cash_receipt_id(+)
         AND arc.TYPE = 'CASH'
         AND ara.display = 'Y'
         AND ara.application_type = 'CASH'
         AND NVL (ara.confirmed_flag, 'Y') = 'Y'
         AND NVL (arc.confirmed_flag, 'Y') = 'Y'
         AND acrh.cash_receipt_id = arc.cash_receipt_id
         AND acrh.first_posted_record_flag = 'Y'
         AND cust.CUST_ACCOUNT_ID(+) = arc.pay_from_customer
         AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
         AND arc.receipt_method_id = arm.receipt_method_id
         AND bat.batch_id(+) = acrh.batch_id
         AND gcc.code_combination_id = aCRH.ACCOUNT_CODE_COMBINATION_ID
         AND ara.applied_customer_trx_id = trx.customer_trx_id
         AND trx.org_id = hou.organization_id
         AND arc.receipt_number BETWEEN NVL (:p_in_cr_number,
                                             arc.receipt_number)
                                    AND  NVL (:p_in_cr_number_high,
                                              arc.receipt_number)
         AND gcc.segment1 BETWEEN NVL (:p_company_from, segment1)
                              AND  NVL (:p_company_to, segment1)
         AND party.party_name BETWEEN NVL (:p_in_customer_low,
                                           party.party_name)
                                  AND  NVL (:p_in_customer_high,
                                            party.party_name)
         AND bat.name BETWEEN NVL (:p_in_batch_low, bat.name)
                          AND  NVL (:p_in_batch_high, bat.name)
         --         AND arc.deposit_date BETWEEN NVL (:p_in_deposit_date_low,
         --                                           arc.deposit_date)
         --                                  AND  NVL (:p_in_deposit_date_high,
         --                                            arc.deposit_date)
         AND arc.status BETWEEN NVL (:p_in_status_low, arc.status)
                            AND  NVL (:p_in_status_high, arc.status)
         AND ara.gl_date BETWEEN NVL (:p_in_gl_date_low, ara.gl_date)
                             AND  NVL (:p_in_gl_date_high, ara.gl_date)
         AND DECODE (NVL (:p_in_currency, arc.currency_code),
                     'All', arc.currency_code,
                     NVL (:p_in_currency, arc.currency_code)) =
               arc.currency_code
      UNION
         ----- NULL CUSTOMER  ------
/* Formatted on 3/26/2013 12:30:47 PM (QP5 v5.114.809.3010) */
SELECT   gcc.segment1 Company,
         bat.NAME batch_name,
         arc.deposit_date deposit_date,
         NULL customer_name,
         NULL customer_number,
         arc.receipt_number receipt_number,
         arc.receipt_date receipt_date,
         arc.STATUS receipt_status,
         acrh.amount receipt_amount,
         gcc.concatenated_segments applied_gl_account,
         acrh.gl_date gl_date,
         NULL applied_date,
         NULL applied_trx_number,
         NULL applied_trx_date
  FROM   ar_cash_receipts_all arc,
         ar_cash_receipt_history_all acrh,
         ar_batches_all bat,
         gl_code_combinations_kfv gcc,
         ar_receipt_methods arm
 WHERE       1 = 1
         AND ARC.PAY_FROM_CUSTOMER IS NULL
         AND acrh.cash_receipt_id = arc.cash_receipt_id
         AND bat.batch_id(+) = acrh.batch_id
         --         AND arc.receipt_number in('1204298-UNITED TEL','300497-TRIANGLE','1202381-UNITED TEL')
         AND gcc.code_combination_id = aCRH.ACCOUNT_CODE_COMBINATION_ID
         AND arc.receipt_method_id = arm.receipt_method_id
         AND arc.receipt_number BETWEEN NVL (:p_in_cr_number,
                                             arc.receipt_number)
                                    AND  NVL (:p_in_cr_number_high,
                                              arc.receipt_number)
         AND gcc.segment1 BETWEEN NVL (:p_company_from, segment1)
                              AND  NVL (:p_company_to, segment1)
--         AND party.party_name BETWEEN NVL (:p_in_customer_low,
--                                           party.party_name)
--                                  AND  NVL (:p_in_customer_high,
--                                            party.party_name)
         AND bat.name BETWEEN NVL (:p_in_batch_low, bat.name)
                          AND  NVL (:p_in_batch_high, bat.name)
         --         AND arc.deposit_date BETWEEN NVL (:p_in_deposit_date_low,
         --                                           arc.deposit_date)
         --                                  AND  NVL (:p_in_deposit_date_high,
         --                                            arc.deposit_date)
         AND arc.status BETWEEN NVL (:p_in_status_low, arc.status)
                            AND  NVL (:p_in_status_high, arc.status)
         AND acrh.gl_date BETWEEN NVL (:p_in_gl_date_low, acrh.gl_date)
                              AND  NVL (:p_in_gl_date_high, acrh.gl_date)
         AND DECODE (NVL (:p_in_currency, arc.currency_code),
                     'All', arc.currency_code,
                     NVL (:p_in_currency, arc.currency_code)) =
               arc.currency_code
=====================================================================

SELECT   bat.NAME batch_name,
         arc.receipt_number receipt_number,
         acrh.gl_date gl_date,
         arc.deposit_date deposit_date,
         arc.STATUS receipt_status,
         acrh.amount receipt_amount,
--         arC.amount,
         gcc.concatenated_segments applied_gl_account,
         arc.receipt_date receipt_date
  FROM   ar_cash_receipts_all arc,
         ar_cash_receipt_history_all acrh,
         ar_batches_all bat,
         gl_code_combinations_kfv gcc,
         ar_receipt_methods arm
 WHERE       1 = 1
         and ARC.PAY_FROM_CUSTOMER  is NULL
         AND acrh.cash_receipt_id = arc.cash_receipt_id
         AND bat.batch_id(+) = acrh.batch_id
         AND gcc.code_combination_id = aCRH.ACCOUNT_CODE_COMBINATION_ID
         AND arc.receipt_method_id = arm.receipt_method_id
         AND acrh.gl_date BETWEEN NVL (:p_in_gl_date_low, acrh.gl_date)
                           AND  NVL (:p_in_gl_date_high, acrh.gl_date)
         AND gcc.segment1 BETWEEN NVL (:p_company_from, segment1)
                              AND  NVL (:p_company_to, segment1)

Wednesday 13 March 2013

To Find The Lock Tables In Oracle Apps

SELECT   c.owner,
         c.object_name,
         c.object_type,
         b.SID,
         b.serial#,
         b.status,
         b.osuser,
         b.machine
  FROM   v$locked_object a, v$session b, dba_objects c
 WHERE   b.SID = a.session_id AND a.object_id = c.object_id;


 =====================   ******************  ========================

SELECT c.owner,
c.object_name,
c.object_type,
fu.user_name locking_fnd_user_name,
fl.start_time locking_fnd_user_login_time,
vs.module,
vs.machine,
vs.osuser,
vlocked.oracle_username,
vs.SID,
vp.pid,
vp.spid AS os_process,
vs.serial#,
vs.status,
vs.saddr,
vs.audsid,
vs.process
FROM fnd_logins fl,
fnd_user fu,
v$locked_object vlocked,
v$process vp,
v$session vs,
dba_objects c
WHERE vs.SID = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
--AND c.object_name LIKE '%' || UPPER ('&tabname_blank4all') || '%'
AND NVL (vs.status, 'XX') != 'KILLED';