Update HZ_Organization_units Table

CREATE OR REPLACE PROCEDURE XX_UPDATE_BRAND_NAME
AS
   L_PERSON_REC              HZ_PARTY_V2PUB.PERSON_REC_TYPE;
   L_ORGANIZATION_REC        HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE;
   L_VERSION_NUMBER          NUMBER;
   L_PARTY_ID                NUMBER;
   L_PARTY_TYPE              VARCHAR2(100);
   L_DUNS_NAME               VARCHAR2 (100);
   L_ORIG_SYSTEM_REFERENCE   VARCHAR2 (100);
   X_PARTY_NUMBER            VARCHAR2 (100);
   X_PARTY_ID                NUMBER;
   X_PROFILE_ID              NUMBER;
   X_RETURN_STATUS           VARCHAR2 (1000);
   X_MSG_COUNT               NUMBER;
   X_MSG_DATA                VARCHAR2 (1000);
   L_ERROR_MESSAGE           VARCHAR2 (1000);

   CURSOR P_RECORDS
   IS
      SELECT *
        FROM XX_BRAND_NAME_STAG where comments is null;
       
BEGIN

   FOR P1 IN P_RECORDS
    LOOP
        IF P1.COMMENTS IS NULL THEN
 
        SELECT PARTY_ID, OBJECT_VERSION_NUMBER,PARTY_TYPE
            INTO L_PARTY_ID, L_VERSION_NUMBER,L_PARTY_TYPE
            FROM HZ_PARTIES
            WHERE PARTY_ID = P1.PARTY_ID;

      IF L_PARTY_TYPE = 'ORGANIZATION'
      THEN
         L_ORGANIZATION_REC.ORGANIZATION_NAME := P1.PARTY_NAME;
        -- L_ORGANIZATION_REC.CREATED_BY_MODULE :=  P1.CREATED_BY_MODULE;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_ID :=  P1.PARTY_ID;
         L_ORGANIZATION_REC.PARTY_REC.PARTY_NUMBER := P1.PARTY_NUMBER;
       --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM_REFERENCE :=  P1.ORIG_SYSTEM_REFERENCE;
         --  L_ORGANIZATION_REC.PARTY_REC.ORIG_SYSTEM           := P_ORIG_SYSTEM;
         L_ORGANIZATION_REC.DUNS_NUMBER_C := P1.BRAND_NAME;

       
            UPDATE APPS.HZ_ORGANIZATION_PROFILES G
               SET DUNS_NUMBER_C =  P1.BRAND_NAME
             WHERE ATTRIBUTE_CATEGORY(+) = 'Organization Information'
               AND (EFFECTIVE_END_DATE IS NULL OR EFFECTIVE_END_DATE > SYSDATE
                   )
               AND PARTY_ID = L_PARTY_ID;
       

         HZ_PARTY_V2PUB.UPDATE_ORGANIZATION
                           (P_INIT_MSG_LIST                    => FND_API.G_TRUE,
                            P_ORGANIZATION_REC                 => L_ORGANIZATION_REC,
                            P_PARTY_OBJECT_VERSION_NUMBER      => L_VERSION_NUMBER,
                            X_PROFILE_ID                       => X_PROFILE_ID,
                            X_RETURN_STATUS                    => X_RETURN_STATUS,
                            X_MSG_COUNT                        => X_MSG_COUNT,
                            X_MSG_DATA                         => X_MSG_DATA
                           );
      -- P_DUNS_NAME =>'URBANOO' );
     
     
     
     END IF;
   UPDATE XX_BRAND_NAME_STAG SET COMMENTS='Y' WHERE PARTY_ID =P1.PARTY_ID;
    COMMIT;
     ELSE
UPDATE XX_BRAND_NAME_STAG SET COMMENTS='N' WHERE PARTY_ID =P1.PARTY_ID;
COMMIT;
   END IF;
   END LOOP;

   FOR I IN 1 .. X_MSG_COUNT
   LOOP
      L_ERROR_MESSAGE :=
            I
         || '. '
         || SUBSTR (FND_MSG_PUB.GET (P_ENCODED => FND_API.G_FALSE), 1, 255);
   --SINA_ERROR('UPDATE_PARTY', P_PARTY_TYPE || P_PARTY_NAME || ' ' || L_ERROR_MESSAGE);
   END LOOP;
 
END XX_UPDATE_BRAND_NAME;

current GL Period

select max(gl_date) from
ra_cust_trx_line_gl_dist_all

To find Responsibilities

-- Query to find the Responsibilities based on Concurrent Request set Name

SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              frsv.user_request_set_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_request_sets_vl frsv
        WHERE fr.request_group_id = frg.request_group_id
          AND fr.responsibility_id = frt.responsibility_id
          AND frg.request_group_id = frgu.request_group_id
          AND frgu.request_unit_id = frsv.request_set_id
          AND frsv.user_request_set_name LIKE 'give user request set name'
          AND frsv.request_set_name LIKE 'request set name';
       

-- Query to find the Responsibilities based on Concurrent Program Name        
     
SELECT UNIQUE frt.responsibility_name, frg.request_group_name,
              fcpt.user_concurrent_program_name
         FROM fnd_responsibility fr,
              fnd_responsibility_tl frt,
              fnd_request_groups frg,
              fnd_request_group_units frgu,
              fnd_concurrent_programs_tl fcpt
        WHERE fr.responsibility_id = frt.responsibility_id
          AND fr.request_group_id = frg.request_group_id
          AND frgu.request_group_id = frg.request_group_id
          AND frgu.request_unit_id = fcpt.concurrent_program_id
          AND fcpt.LANGUAGE = 'US'
          AND frt.LANGUAGE = 'US'
          AND fcpt.user_concurrent_program_name LIKE
                                                'Your Concurrent Program Name'        

AR Tables 11i

Query for Subledger Transfer to GL

SELECT    gjjlv.period_name             "Period Name"
        , gjb.name                      "Batch Name"
        , gjjlv.header_name             "Journal Entry For"
        , gjjlv.je_source               "Source"
        ,glcc.concatenated_segments     "Accounts"
        , NVL(gjjlv.line_entered_dr,0)  "Entered Debit"
        , NVL(gjjlv.line_entered_cr,0)   "Entered Credit"
        , NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
        , NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
        , gjjlv.currency_code            "Currency"
        , rctype.name                    "Trx type"
        , rcta.trx_number                "Trx Number"
        , rcta.trx_date                  "Trx Date"
        , RA.CUSTOMER_NAME               "Trx Reference"
        , gjh.STATUS                     "Posting Status"
        , TRUNC(gjh.DATE_CREATED)        "GL Transfer Dt"
        , gjjlv.created_by               "Transfer By"
FROM    apps.GL_JE_JOURNAL_LINES_V gjjlv
      , gl_je_lines gje
      , gl_je_headers gjh
      , gl_je_batches gjb
      , ra_customer_trx_all rcta
      , apps.ra_customers ra
      , apps.gl_code_combinations_kfv glcc
      , ra_cust_trx_types_all rctype
WHERE     gjh.period_name IN ('OCT-2008','NOV-2008')
AND       glcc.code_combination_id = gje.code_combination_id
AND       gjh.je_batch_id = gjb.je_batch_id
AND       gjh.je_header_id = gje.je_header_id
AND       gjh.period_name = gjb.default_period_name
AND       gjh.period_name = gje.period_name
AND       gjjlv.period_name = gjh.period_name
AND       gjjlv.je_batch_id = gjh.je_batch_id
AND       gjjlv.je_header_id = gjh.je_header_id
AND       gjjlv.line_je_line_num  = gje.je_line_num
AND       gjjlv.line_code_combination_id = glcc.code_combination_id
AND       gjjlv.line_reference_4 = rcta.trx_number
AND       rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND       rcta.org_id = rctype.org_id
AND       ra.customer_id = rcta.bill_to_customer_id

AR Useful Query

Query to fetch summary data*/

SELECT hc.cust_account_id          
      ,min(hc.account_number)         customer_number
      ,sum(amount_due_remaining)      amount_due_remaining
      ,sum(aps.amount_due_original)   amount_due_original
      ,count(aps.payment_schedule_id) open_invoices
  FROM ra_customer_trx_all       ra,
       ra_customer_trx_lines_all rl,
       ar_payment_schedules_all  aps,
       ra_cust_trx_types_all     rt,
       hz_cust_accounts          hc,
       hz_parties                hp,
       hz_cust_acct_sites_all    hcasa_bill,
       hz_cust_site_uses_all     hcsua_bill,
       hz_party_sites            hps_bill,
       ra_cust_trx_line_gl_dist_all rct
 WHERE ra.customer_trx_id           = rl.customer_trx_id
   AND ra.customer_trx_id           = aps.customer_trx_id
   AND ra.org_id                    = aps.org_id
   AND rct.customer_trx_id          = aps.customer_trx_id
   AND rct.customer_trx_id          = ra.customer_trx_id
   AND rct.customer_trx_id          = rl.customer_trx_id
   AND rct.customer_trx_line_id     = rl.customer_trx_line_id
   AND ra.complete_flag             = 'Y'
   AND rl.line_type                 IN ('FREIGHT', 'LINE')
   AND ra.cust_trx_type_id          = rt.cust_trx_type_id
   AND ra.bill_to_customer_id       = hc.cust_account_id
   AND hc.status                    = 'A'
   AND hp.party_id                  = hc.party_id
   AND hcasa_bill.cust_account_id   = ra.bill_to_customer_id
   AND hcasa_bill.cust_acct_site_id = hcsua_bill.cust_acct_site_id
   AND hcsua_bill.site_use_code     = 'BILL_TO'
   AND hcsua_bill.site_use_id       = ra.bill_to_site_use_id
   AND hps_bill.party_site_id       = hcasa_bill.party_site_id
   AND hcasa_bill.status            = 'A'
   AND hcsua_bill.status            = 'A'
   AND aps.amount_due_remaining     <> 0
   AND aps.status                   = 'OP'
   GROUP by hc.cust_account_id;

AR Useful Query

Sharing few queries Which i have came across while browsing :-


TRACE WHICH AR INVOICE IS LINKED WITH WHICH GL ENTRY.

Linking Table between AR to GL :

In R12, try using the query below:

SELECT b.NAME batch_name,
b.description batch_description,
h.je_category,
h.je_source,
h.period_name je_period_name,
h.NAME journal_name,
h.status journal_status,
h.description je_description,
l.je_line_num line_number
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_code_combinations_kfv glcc,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla.xla_transaction_entities xlate,
ra_customer_trx_all rct,
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND xlal.code_combination_id = glcc.code_combination_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
AND h.je_source = 'Receivables'
AND h.period_name = '2009-10'
AND rct.trx_number = xlate.transaction_number
AND xlate.transaction_number =''





11i

select gjjlv.period_name "Period Name"
, gjb.name "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, rctype.name "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, gjh.STATUS "Posting Status"
, trunc(gjh.DATE_CREATED) "GL Transfer Dt"
, gjjlv.created_by "Transfer By"
from apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
where /*gjh.period_name IN ('OCT-2008','NOV-2008')
and */glcc.code_combination_id = gje.code_combination_id
and gjh.je_batch_id = gjb.je_batch_id
and gjh.je_header_id = gje.je_header_id
and gjh.period_name = gjb.default_period_name
and gjh.period_name = gje.period_name
and gjjlv.period_name = gjh.period_name
and gjjlv.je_batch_id = gjh.je_batch_id
and gjjlv.je_header_id = gjh.je_header_id
and gjjlv.line_je_line_num = gje.je_line_num
and gjjlv.line_code_combination_id = glcc.code_combination_id
and gjjlv.line_reference_4 = rcta.trx_number
and rcta.cust_trx_type_id = rctype.cust_trx_type_id
and rcta.org_id = rctype.org_id
and ra.customer_id = rcta.bill_to_customer_id

Happy Reading.....................

AR Tables 11i

RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLES_TRX_ALL
AR_RECEIVABLE_APPLICATIONS_ALL


transaction types stored in RA_CUST_TRX_TYPES_ALL

RA_CUSTOMER_TRX_ALL
--------------------------


The primary key for this table is CUSTOMER_TRX_ID.

-If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID
 stores the customer transaction identifier of the invoice you credited,otherwise it is null.

-invoice against a commitment  INITIAL_CUSTOMER_TRX_ID,otherwise it is null.

-COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No




RA_CUSTOMER_TRX_LINES_ALL
---------------------------------


-The primary key for this table is CUSTOMER_TRX_LINE_ID
-QUANTITY_ORDERED stores the amount of product ordered
-QUANTITY_INVOICED stores the amount of product invoiced

**For invoices entered through the window, QUANTITY_ORDERED and QUANTITY_INVOICED must be the same.
  For invoices imported through AutoInvoice, QUANTITY_ORDERED and QUANTITY_INVOICED can be different**

-If you enter a credit memo, QUANTITY_CREDITED stores the amount of product credited
-UOM_CODE stores the unit of measure code as defined in MTL_UNITS_OF_MEASURE
-UNIT_STANDARD_PRICE stores the list price per unit for this transaction line.
 UNIT_SELLING_PRICE stores the selling price per unit for this transaction line.
 For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and UNIT_SELLING_PRICE can be different.
-**LINE_TYPE differentiates between the different types of lines that are stored in this table.
LINE points to regular invoice lines that normally refer to an item. **

--For every row in this table that belongs to a complete transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y),
  there must be at least one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting information),
  even for non–postable transactions.


RA_CUST_TRX_LINE_GL_DIST_ALL
----------------------------------------------

The primary key for this table is CUST_TRX_LINE_GL_DIST_ID.

ACCOUNT_SET_FLAG is Y if this row is part of an account set

AR_PAYMENT_SCHEDULES_ALL
-----------------------------------------------

Oracle Receivables groups different transactions bythe column CLASS.
These classes include invoice (INV),
debit memos(DM), guarantees (GUAR),
credit memos (CM), deposits (DEP),
chargebacks (CB), and receipts (PMT).

The primary key for this table is PAYMENT_SCHEDULE_ID

--AR_PAYMENT_SCHEDULES_ALL joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries
such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits.

--AR_PAYMENT_SCHEDULES_ALL uses the foreign key CUSTOMER_TRX_ID to join to the RA_CUSTOMER_TRX_ALL
table for these transactions.

--AR_PAYMENT_SCHEDULES_ALL joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions using the
foreign key CASH_RECEIPT_ID.

--When a receiptis applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS and AMOUNT_DUE_REMAINING.

--STATUS changes from ’OP’ to ’CL’for any transaction that has an AMOUNT_DUE_REMAINING value of 0(Zero)

--The sum of the AMOUNT_DUE_REMAINING column for a customer for all confirmed payment schedules reflects the current customer balance. If this amount is negative, then this column indicates the credit balance amount currently available for this customer.

--For invoices with split terms, one record is created in RA_CUSTOMER_TRX_ALL and one record is stored in AR_PAYMENT_SCHEDULES_ALL for each installment.

--In AR_PAYMENT_SCHEDULES_ALL, DUE_DATE and AMOUNT_DUE_REMAINING can differ for each installment of a split term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER column.



NOTE:
If you create a debit memo reversal when you reverse a receipt, Oracle Receivables creates a new payment schedule record for the
debit memo and fills in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was reversed.
Oracle Receivables creates a new payment schedule record when you create a chargeback in the Receipts window.
ASSOCIATED_CASH_RECEIPT_ID is the cash receipt of the payment you entered when you created the chargeback in this window.
GL_DATE_CLOSED indicates the general ledger date on which your transaction was closed.

AR_RECEIVABLES_TRX_ALL
------------------------------

Adjustment, Miscellaneous Cash, and Finance Charge

The primary key for this table is RECEIVABLES_TRX_ID.

AR_RECEIVABLE_APPLICATIONS_ALL
-----------------------------------


Possible statuses of your applications include APP, UNAPP, ACC, and UNID

There are two kinds of applications: CASH and CM (for credit memo applications). This is stored in the column APPLICATION_TYPE.