AR To GL Via Subledger

SELECT ACCOUNT_NUMBER,
ACCOUNT_NAME,
TRX.TRX_NUMBER INVOICE_NUMBER,
GJB.NAME BATCH_NAME,GJH.NAME JOURNAL_NAME,
GJH.RUNNING_TOTAL_ACCOUNTED_DR TOTAL_DEBIT_AMOUNT,
GJH.RUNNING_TOTAL_ACCOUNTED_CR TOTAL_CREDIT_AMOUNT,
GIR.REFERENCE_9 INVOICE_AMOUNT, ACCOUNTING_CLASS_CODE
FROM GL_JE_HEADERS GJH, 
GL_JE_LINES GJL,
GL_JE_BATCHES GJB,
GL_IMPORT_REFERENCES GIR, 
XLA_AE_LINES XAL, 
XLA_AE_HEADERS XAH, 
XLA_DISTRIBUTION_LINKS LK,
RA_CUST_TRX_LINE_GL_DIST_ALL GL_DIST,
RA_CUSTOMER_TRX_ALL TRX,
HZ_CUST_ACCOUNTS_ALL CUST_ACCT
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID 
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID
--AND GJH.NAME = 'APR-10 DEBIT MEMOS USD'
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GJB.JE_BATCH_ID = GIR.JE_BATCH_ID
AND JE_SOURCE = 'RECEIVABLES'
AND GJH.STATUS = 'P'
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID 
AND GIR.GL_SL_LINK_TABLE= XAL.GL_SL_LINK_TABLE
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND LK.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL' 
AND LK.APPLICATION_ID = 222 
AND LK.AE_HEADER_ID = XAL.AE_HEADER_ID 
AND LK.AE_LINE_NUM = XAL.AE_LINE_NUM
AND LK.AE_HEADER_ID = XAH.AE_HEADER_ID 
AND XAL.ACCOUNTING_CLASS_CODE IN ('RECEIVABLE','REVENUE')
AND GIR.REFERENCE_9 IS NOT NULL
AND GL_DIST.CUST_TRX_LINE_GL_DIST_ID = LK.SOURCE_DISTRIBUTION_ID_NUM_1
AND GL_DIST.ACCOUNT_SET_FLAG = 'N'
--AND GL_DIST.ACCOUNT_CLASS = 'REV'
AND GL_DIST.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID
AND TRX.COMPLETE_FLAG = 'Y'
AND CUST_ACCT.CUST_ACCOUNT_ID = TRX.BILL_TO_CUSTOMER_ID
AND TRX_NUMBER = '900029562'
AND GJH.JE_HEADER_ID = 414923
AND GJB.JE_BATCH_ID = 422909

Difference between expense item and inventory item

Those Item that needs to maintain stock and tracking are inventory Items. Creating unique Item coding for each SKU's

Non-Stock able Items that is direct IN & OUT, are expense items. For such items no need to create Item code for all. Only few codes can be created and in PR & PO description can be change

For example

Inventory items: Machine parts, Raw Materials, Any Trading Items etc

Expenses Items: Assets, services, Projects, consumables (Office Stationery) etc.

You cannot define an item as expense and inventoried at the same time. But you can define the item as inventory item.

And when you want to use it as expense, move it to an expense subinventory.

We need to uncheck the attribute "Asset Subinventory" in the specified subinventory.

You should uncheck the asset flag for that subinventory. Make sure that the subinventory accounts are setup correctly.

The terminology of items is rather confusing from an Purchasing/Inventory point of view:

For easy understanding these will be referres to as A , B and C,

A - Expense Items

B - Inventory Expense Items

C - Inventory Asset Items

A - These items have attributes checked

a - Purchasable

b - Purchased

B - These tems have the following attributes checked

a - inventory item = YES

b - stockable

c - transactable

d - Inventory Asset Value = NO

e - Costing Enabled = No

C - These items have the following attributes checked

a - inventory item = YES

b - stockable.

c - transactable

d - Inventory Asset Value = YES

e - Costing Enabled = YES

As you know "procure to pay" Business Flow start Purchasing requisition till paying to vendors and most important, in all the case the purchase is made for basic element called Items.

As you know there are three types of items:

• Inventory Expense Item
• Inventory Asset Item
• Expense item

Definition of above Items used in Purchasing can be best understood as:

Asset flag means means it is an asset and the items value will show in your inventory valuation.

Inventory Item

Expense Item

These are one which is used for consumable items purchase for your organization. More importantly , for creating an expense item you have to perform following setup doing in the Master Item form.Go to same path in oracle inventory

Oracle Inventory -> Items -> Master Items

When master items form open Go to Inventory Menu you need to tick followings

1. Inventory item
2. Stock able
3. Transactble
4. Resolvable

And you can also setup in Costing and purchasing menu account code as per your requirement.

Asset Item

As discussed above , the following attributes need to be enabled for such an item.

• Inventory item
• Stock able
• transact able
• Costing flag
• Inventory asset value

For entering on purchase orders
It should have purchased and purchasable flags enabled and you have to make sure you are assigning this item to the Purchasing org which you have defined at

Oracle Purchasing > Setup > Organizations >
Financial Options > 'Supplier-Purchasing' alternate region 'Inventory Organization' field.

The accounting can be best described for such kind of items is;

Is there any effect on Step 5 in all three cases, that mean do matching have different accounting entry?

The answer is no; as per my understanding purpose of setting the PO to a 2way, 3 way or 4 way match is to ensure that the corresponding hold is generated on the invoice.

The holds are basically designed for control purposes, they do not have any accounting effects.

1. What is the setup for expense item?

    Ans: Disable the asset and costing flags in Item master costing tab

2. Significance of Purchasable & Purchased option?

   Ans:  Purchasable is an item defining attribute, it tells that item can be purchased from external  Suppliers

Purchased is an item status attribute, item can be purchased in this status

3. Significance of Customer Ordered & Customer Orders enabled?

Ans: CO: Item defining attribute

COE: item status attribute

 Create XML Publisher report without RDF

 1. Create a Data Definition using XML Publisher Administration Responsibility As shown below.

2. Click on Create Data Definition button and the details (Make sure you enter the concurrent program short name in Code) and click on Apply button

3. Go back to Data definition and add the Data Template file as shown below (attached sample template file for your reference , make necessary changes to template for your report

<?xml version = "1.0" encoding = "UTF-8"?>
<dataTemplate name="XXAR_CLIENT_DET_CASH_PYMT" version="1.0">
<properties>
<property name="debug_mode" value="on"/>
</properties>
 <parameters>
  </parameters>
<dataQuery>
   <sqlStatement name="Q_CLNT_DET">
       <![CDATA[
       select   distinct hca.account_number,account_name,
         hl.address4 Legacy_num,hca.attribute2 LOB,
         decode(hcsua.org_id,2,'US',22,'CAN') org_id,
         decode(hca.attribute12,'Y','CASH') Payment_Mode,
         trunc(TO_DATE(acrmv.start_date,'DD-MON-YY')) START_DATE,trunc(TO_DATE(acrmv.end_date,'DD-MON-YY')) END_DATE,
         acrm.name PAYMENT_METHOD,
         hcsua.site_use_code,
         hl.address1||','||hl.address2||','||hl.address3||','||hl.address4||','||hl.CITY||','||hl.state||','||hl.POSTAL_CODE||','||hl.country BILL_TO_ADDRESS
from
hz_locations hl,
HZ_PARTY_SITES hps,
hz_parties party,
HZ_CUST_ACCOUNTS hca,
HZ_CUST_ACCT_SITES_ALL hcasa,
HZ_CUST_SITE_USES_ALL hcsua,
HZ_CUSTOMER_PROFILES hcp,
ar_cust_receipt_methods_v acrmv,
ar_receipt_methods acrm
where
hps.location_id=hl.location_id
and party.party_id=hps.party_id
and party.party_id=hca.party_id
and hcasa.CUST_ACCOUNT_ID=hca.CUST_ACCOUNT_ID
and hcsua.CUST_ACCT_SITE_ID=hcasa.CUST_ACCT_SITE_ID
and hcp.SITE_USE_ID=hcsua.SITE_USE_ID
and hps.party_site_id=hcasa.party_site_id
and hca.cust_account_id = acrmv.customer_id
and acrmv.receipt_method_id = acrm.receipt_method_id
and hca.attribute12 = 'Y'
and hcsua.site_use_code = 'BILL_TO'
AND hcasa.BILL_TO_FLAG = 'P'
--and rownum = 1
      ]]>
   </sqlStatement>

</dataQuery>
<dataStructure>
   
         <group name="G_CLNT_DET" source="Q_CLNT_DET">
                    <element name ="ACCOUNT_NAME" value="ACCOUNT_NAME"/>
                    <element name ="ACCOUNT_NUMBER" value="ACCOUNT_NUMBER"/>
                    <element name ="LEGACY_NUM" value="LEGACY_NUM"/>           
                    <element name ="LOB" value="LOB"/>
                    <element name ="ORG_ID" value="ORG_ID"/>
                    <element name ="PAYMENT_MODE" value="PAYMENT_MODE"/>
                    <element name ="START_DATE" value="START_DATE"/>
                    <element name ="END_DATE" value="END_DATE"/>
                    <element name ="PAYMENT_METHOD" value="PAYMENT_METHOD"/>
                    <element name ="SITE_USE_CODE" value="SITE_USE_CODE"/>
                    <element name ="BILL_TO_ADDRESS" value="BILL_TO_ADDRESS"/>
                                     
   </group>
</dataStructure>
</dataTemplate>

4. Once you create the Data definition, please create the respective concurrent program for this report if one does not already exists. Make sure the execute for this concurrent program should XDODTEXE

5.    Make sure to enter the Token in the parameters screen. Also noted that this token should match to your parameters name in Data Template file.

6.    Once finished creation of this, please go ahead and create Template for this concurrent program using xml file generated from output of this concurrent request.

Oracle PO Interface and Requisition Interface

Purchase Order Interface:

Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information 
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information 
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id 
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory 
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory 
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program: 
apps.fnd_request.submit_request (application => 'PO' --Application, 
program => 'POXPOPDOI'--Program, 
argument1 => ''--Buyer ID, 
argument2 => 'STANDARD'--Document Type, 
argument3 => ''--Document Subtype, 
argument4 => 'N'--Process Items Flag, 
argument5 => 'N'--Create Sourcing rule, 
argument6 => ''--Approval Status, 
argument7 => ''--Release Generation Method, 
argument8 => ''--NULL, 
argument9 => g_org_id--Operating Unit ID, 
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);

Requisition Interface:

-- insert data into Interface tables
INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
(interface_source_code
,source_type_code
,requisition_type
,destination_type_code
,item_id
,item_description
,quantity
,authorization_status
,preparer_id
,autosource_flag
,req_number_segment1 *** see the note
,header_attribute13 ---xtra infomation 
,line_attribute15 ---xtra infomation 
,uom_code
,destination_organization_id
,destination_subinventory
,deliver_to_location_id
,deliver_to_requestor_id
,need_by_date
,gl_date
,charge_account_id
,accrual_account_id
,variance_account_id
,org_id
,suggested_vendor_id
,suggested_vendor_site_id
,unit_price
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES ('INV'
,'VENDOR'
,'PURCHASE'
,'INVENTORY'
,rec_get_lines_info.inventory_item_id
,rec_get_lines_info.item_desc
,rec_get_lines_info.ordered_quantity
,l_authorization_status --------'INCOMPLETE' or 'APPROVED'
,g_employee_id
,'P'
,l_req_segment1
,'ZZ' ---xtra infomation 
,rec_get_lines_info.ship_to_org_id ---xtra infomation 
,rec_get_lines_info.uom_code
,rec_get_lines_info.ship_from_org_id
,rec_get_lines_info.subinventory
,rec_get_lines_info.location_id
,get_requestor (fnd_global.user_id) --rec_get_lines_info.requestor
,rec_get_lines_info.schedule_ship_date
,SYSDATE
,rec_get_lines_info.charge_account
,rec_get_lines_info.ap_accrual_account
,rec_get_lines_info.invoice_price_var_account
,g_org_id
, rec_get_lines_info.vendor_id 
, rec_get_lines_info.vendor_site_id 
,rec_get_lines_info.list_price
,SYSDATE
,fnd_global.user_id
,SYSDATE
,fnd_global.user_id
); 


Note:

Standard grouping rules provided by Oracle are by
Buyer
Category
Item
Location
Vendor or
ALL , these grouping rules can be over written by populating "req_number_segment1" .
When we use req_number_segment1 with ALL grouping option, requisiton will be grouped by req_number_segment1

Concurrent Program: 

apps.fnd_request.submit_request (application => 'PO' --Application, 
program => 'REQIMPORT' --Program, 
argument1 => 'INV' --Interface Source code, 
argument2 => '' --Batch ID, 
argument3 => 'ALL'--Group By, 
argument4 => ''--Last Req Number, 
argument5 => ''--Multi Distributions, 
argument6 => 'N' --Initiate Approval after ReqImport 
);