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;

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.

Receipt API

A simple Example

Declare

l_cr_id number;
l_return_status varchar2(20000);
l_msg_count number;
l_msg_data varchar2(20000);
p_count number;
begin
dbms_output.put_line('Start');
fnd_global.apps_initialize( 1297,20678,222,0);
arp_standard.enable_debug;
Ar_receipt_api_pub.Create_cash(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
p_receipt_number => '431',
p_customer_bank_account_id=>'10024',
p_amount => 10000,
p_currency_code=>'QAR',
p_commit=>FND_API.G_TRUE,
p_receipt_method_id =>3001,
p_customer_id => 3043,
p_called_from => 'BR_FACTORED_WITH_RECOURSE',
p_receipt_date=>to_date('12-11-2007','dd-mm-yyyy'),
p_maturity_date=>to_date('12-11-2007','dd-mm-yyyy'),
p_gl_date=>to_date('30-6-2004','dd-mm-yyyy'),
p_cr_id => l_cr_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
dbms_output.put_line( l_cr_id

'l');
dbms_output.put_line( l_return_status);
dbms_output.put_line( l_msg_data );
dbms_output.put_line( l_msg_count);
IF l_msg_count = 1 Then
dbms_output.put_line('l_msg_data '

l_msg_data);
ELSIF l_msg_count > 1 Then
LOOP
p_count := p_count+1;
l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_msg_data is NULL Then
EXIT;
END IF;
dbms_output.put_line('Message'

p_count

'.'

l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
end;

AR TABLES

• RA_CUSTOMER_TRX_ALL :stores invoice header information.
• RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.
• RA_CUSTOMERS – Customer information
• RA_CUST_TRX_TYPES_ALL – Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• AR_CASH_RECEIPTS_ALL stores one record for each receipt entry
• AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
• AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
• AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.

AR LOCKBOX

AutoLockbox automated tool used to receipts which are sent to bank directly.


It Validates data before transferring then to recievables.
 
Steps Performed by AR Lockbox:


Make required Functional setups for defining Lockbox.

1.Create Transmission format.

• 
  
  Give Transmission Name
• 
  
  LOCKBOX HEADER,RECIPET,OVERFLOW RECIPET
• 
  
  LOCKBOX HEADER with identifier:

SAMPLE CONTROL FILE:

LOAD DATA

APPEND

-- Type R2- Overflow Receipt

INTO TABLE AR_PAYMENTS_INTERFACE_ALL

WHEN RECORD_TYPE = ’R2’

(STATUS CONSTANT ’AR_PLB_NEW_RECORD’,

RECORD_TYPE POSITION(01:02) CHAR,

LOCKBOX_NUMBER POSITION(03:05) CHAR,

INVOICE1 POSITION(06:19) CHAR,

AMOUNT_APPLIED1 POSITION(20:30) CHAR,

OVERFLOW_SEQUENCE POSITION(31:34) CHAR,

OVERFLOW_INDICATOR POSITION(35:35) CHAR,

ITEM_NUMBER POSITION(36:38) CHAR,

CURRENCY_CODE POSITION(39:41) CHAR)

-- Type R1 - Payment

INTO TABLE AR_PAYMENTS_INTERFACE_ALL

WHEN RECORD_TYPE = ’R1’

(STATUS CONSTANT ’AR_PLB_NEW_RECORD’,

RECORD_TYPE POSITION(01:02) CHAR,

LOCKBOX_NUMBER POSITION(03:05) CHAR,

BATCH_NAME POSITION(06:08) CHAR,

TRANSIT_ROUTING_NUMBER POSITION(09:18) CHAR,

ACCOUNT POSITION(19:36) CHAR,

CHECK_NUMBER POSITION(37:46) CHAR,

REMITTANCE_AMOUNT POSITION(47:56) CHAR,

DEPOSIT_DATE POSITION(57:62) DATE ’RRMMDD’,

ITEM_NUMBER POSITION(63:65) CHAR,

CURRENCY_CODE POSITION(66:68) CHAR,

DEPOSIT_TIME POSITION(69:72) CHAR)

-- Type R0 - Lockbox Header

INTO TABLE AR_PAYMENTS_INTERFACE_ALL

WHEN RECORD_TYPE = ’R0’

(STATUS CONSTANT ’AR_PLB_NEW_RECORD’,

RECORD_TYPE POSITION(01:02) CHAR,

LOCKBOX_NUMBER POSITION(03:05) CHAR,

ORIGINATION POSITION(06:14) CHAR)

2.Create a loader file(Flat File) in specified format.

Sample Data File:

R0P14043000096

R1P1400002130937 612955 20002914640005587025990901018USD2003

R2P142424068 0000102900000010018USD


3.Place the control file in AR_Top bin directory.

4.In AR run the interface program lockbox with transmission name,control file,data file with path.

Note: Give the control file name without extension and path.

5.sumbit for validation(to get the receipts transferred to base tables.

5.sumbit the lockbox interface program.

6.the output will have the details of transferred recepits.

Interface tables involved are




WITHOUT vALIDATION



AR_PAYMENTS_INTERFACE_ALL



FOR VALIDATION



AR_INTERIM_CASH_RECEIPTS_ALL and

AR_INTERIM_CASH_RCPT_LINES_ALL



BASE TABLES



AR_CASH_RECEIPTS_ALL

AR_CASH_RECEIPT_HISTORY_ALL

AR_DISTRIBUTIONS_ALL

AR_RECEIVABLE_APPLICATIONS_ALL

AR_PAYMENT_SCHEDULES_ALL