AR TO GL LINK IN R12( THROUGH XLA) or ( WITH XLA )
/* Formatted on 11/29/2013 4:46:26 PM (QP5 v5.114.809.3010) */
SELECT ooha.order_number,
ooha.org_id,
hca.account_name,
hp.party_name "Customer Name",
hcasab.orig_system_reference BILL_TO_ORIG_REF,
hpsb.status BILL_TO_STATUS,
'ADDRESS1 - '
|| bill_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| bill_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| bill_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| bill_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| bill_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| bill_loc.country
BILL_TO_ADDRESS,
hcasas.orig_system_reference SHIP_TO_ORIG_REF,
hpss.status SHIP_TO_STATUS,
'ADDRESS1 - '
|| ship_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| ship_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| ship_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| ship_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| ship_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| ship_loc.country
SHIP_TO_ADDRESS,
oola.inventory_item_id,
oola.ordered_item,
msib.description item_description,
wnd.name delivery_number,
rct.trx_number "AR Invoice Number",
acr.receipt_number "AR Receipt Number",
gjh.ledger_id,
gjh.name
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hpss,
hz_party_sites hpsb,
hz_locations bill_loc,
hz_locations ship_loc,
hz_cust_acct_sites_all hcasab,
hz_cust_acct_sites_all hcasas,
hz_cust_site_uses_all hzsuab,
hz_cust_site_uses_all hzsuas,
mtl_system_items_b msib,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctld,
ar_cash_receipts_all acr,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
AND ooha.org_id = 204
AND hca.cust_account_id = ooha.sold_to_org_id
AND hp.party_id = hca.party_id
AND hpss.party_id = hca.party_id
AND hpsb.party_id = hca.party_id
AND bill_loc.location_id = hpss.location_id
AND ship_loc.location_id = hpsb.location_id
AND hcasas.cust_account_id = hca.cust_account_id
AND hcasab.cust_account_id = hca.cust_account_id
AND hcasas.party_site_id = hpss.party_site_id
AND hcasab.party_site_id = hpsb.party_site_id
AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND hzsuas.site_use_id = ooha.ship_to_org_id
AND hzsuab.site_use_id = ooha.invoice_to_org_id
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND wdd.source_line_id = oola.line_id
AND wdd.organization_id = msib.organization_id(+)
AND wdd.inventory_item_id = msib.inventory_item_id(+)
AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
AND rct.org_id = ooha.org_id
AND rctl.customer_trx_id = rct.customer_trx_id
AND rctl.sales_order = TO_CHAR (ooha.order_number)
AND rctld.customer_trx_id = rct.customer_trx_id
AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
AND acr.receipt_number = 'G-1001'
AND acr.pay_from_customer = rct.sold_to_customer_id
AND acr.org_id = ooha.org_id
AND acr.customer_site_use_id = rct.bill_to_site_use_id
AND xte.transaction_number = acr.receipt_number
AND xte.entity_code = 'RECEIPTS'
AND xe.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.accounting_class_code = 'CASH'
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
--and xdl.source_distribution_id_num_1
AND gir.reference_5 = xte.entity_id -- Entity Id
AND gir.reference_6 = TO_CHAR (xe.event_id) --Event Id
AND gir.reference_7 = TO_CHAR (xah.ae_header_id) -- AE Header Id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.created_by = 1318
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gir.je_line_num
/* Formatted on 11/29/2013 4:46:26 PM (QP5 v5.114.809.3010) */
SELECT ooha.order_number,
ooha.org_id,
hca.account_name,
hp.party_name "Customer Name",
hcasab.orig_system_reference BILL_TO_ORIG_REF,
hpsb.status BILL_TO_STATUS,
'ADDRESS1 - '
|| bill_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| bill_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| bill_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| bill_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| bill_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| bill_loc.country
BILL_TO_ADDRESS,
hcasas.orig_system_reference SHIP_TO_ORIG_REF,
hpss.status SHIP_TO_STATUS,
'ADDRESS1 - '
|| ship_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| ship_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| ship_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| ship_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| ship_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| ship_loc.country
SHIP_TO_ADDRESS,
oola.inventory_item_id,
oola.ordered_item,
msib.description item_description,
wnd.name delivery_number,
rct.trx_number "AR Invoice Number",
acr.receipt_number "AR Receipt Number",
gjh.ledger_id,
gjh.name
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hpss,
hz_party_sites hpsb,
hz_locations bill_loc,
hz_locations ship_loc,
hz_cust_acct_sites_all hcasab,
hz_cust_acct_sites_all hcasas,
hz_cust_site_uses_all hzsuab,
hz_cust_site_uses_all hzsuas,
mtl_system_items_b msib,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctld,
ar_cash_receipts_all acr,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
AND ooha.org_id = 204
AND hca.cust_account_id = ooha.sold_to_org_id
AND hp.party_id = hca.party_id
AND hpss.party_id = hca.party_id
AND hpsb.party_id = hca.party_id
AND bill_loc.location_id = hpss.location_id
AND ship_loc.location_id = hpsb.location_id
AND hcasas.cust_account_id = hca.cust_account_id
AND hcasab.cust_account_id = hca.cust_account_id
AND hcasas.party_site_id = hpss.party_site_id
AND hcasab.party_site_id = hpsb.party_site_id
AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND hzsuas.site_use_id = ooha.ship_to_org_id
AND hzsuab.site_use_id = ooha.invoice_to_org_id
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND wdd.source_line_id = oola.line_id
AND wdd.organization_id = msib.organization_id(+)
AND wdd.inventory_item_id = msib.inventory_item_id(+)
AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
AND rct.org_id = ooha.org_id
AND rctl.customer_trx_id = rct.customer_trx_id
AND rctl.sales_order = TO_CHAR (ooha.order_number)
AND rctld.customer_trx_id = rct.customer_trx_id
AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
AND acr.receipt_number = 'G-1001'
AND acr.pay_from_customer = rct.sold_to_customer_id
AND acr.org_id = ooha.org_id
AND acr.customer_site_use_id = rct.bill_to_site_use_id
AND xte.transaction_number = acr.receipt_number
AND xte.entity_code = 'RECEIPTS'
AND xe.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.accounting_class_code = 'CASH'
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
--and xdl.source_distribution_id_num_1
AND gir.reference_5 = xte.entity_id -- Entity Id
AND gir.reference_6 = TO_CHAR (xe.event_id) --Event Id
AND gir.reference_7 = TO_CHAR (xah.ae_header_id) -- AE Header Id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.created_by = 1318
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gir.je_line_num
No comments:
Post a Comment