VIEW: APPS.JAI_PO_REC_AEL_V

Source

View Text - Preformatted

SELECT /*+ ORDERED USE_NL(jrh jrl) USE_NL(jrl jrt) USE_NL(jrt rsl) USE_NL(jrt jrrl) USE_NL(jrt gjr) USE_NL(gjr gjh) USE_NL(gjh gjl) USE_NL(gjh gjs) USE_NL(gjh gjc) USE_NL(gjh glct) INDEX(gjr GL_IMPORT_REFERENCES_N3) */ 7000 Application_Id, 'PO Receipt' event_type, gjl.description event_description, 'JAI PO REC' trx_class, 'JAI PO REC' trx_class_name, jrt.transaction_type trx_type, jrt.shipment_header_id trx_hdr_id, 'jai_rcv_transactions' trx_hdr_table, jrt.organization_id inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jrt.last_update_date LAST_UPDATE_DATE, jrt.last_updated_by LAST_UPDATED_BY, jrt.creation_date CREATION_DATE, jrt.created_by CREATED_BY, jrt.last_update_login LAST_UPDATE_LOGIN, rsl.line_num receipt_line_number, jrt.inventory_item_id item_id, NULL uom, jrt.quantity quantity, NULL location, rsl.to_subinventory sub_inventory, NVL (jrh.excise_invoice_no, jrl.excise_invoice_no) excise_inv_num, NVL (jrh.excise_invoice_date, jrl.excise_invoice_date) excise_invoice_date, NVL (jrt.vat_invoice_no, jrrl.invoice_no) vat_inv_num, NVL (jrt.vat_invoice_date, jrrl.invoice_date) vat_invoice_date, NULL party_name, NULL party_number, NULL party_site, jrt.receipt_num receipt_num, jrt.transaction_date receipt_date, jrh.shipment_num shipment_num, jrh.shipped_date shipment_date FROM jai_rcv_headers jrh, jai_rcv_lines jrl, jai_rcv_transactions jrt, rcv_shipment_lines rsl, jai_rcv_rgm_lines jrrl, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl , gl_je_sources gjs, gl_je_categories gjc, gl_daily_conversion_types glct WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND rsl.shipment_header_id = jrt.shipment_header_id AND rsl.shipment_line_id = jrt.shipment_line_id AND jrl.shipment_header_id = jrt.shipment_header_id AND jrh.shipment_header_id = jrl.shipment_header_id AND jrl.shipment_line_id = jrt.shipment_line_id AND jrrl.shipment_header_id(+) = jrt.shipment_header_id AND jrrl.shipment_line_id(+) = jrt.shipment_line_id AND jrh.receipt_source_code IN ('VENDOR', 'INTERNAL ORDER') AND jrh.shipment_header_id = jrt.shipment_header_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND (gjr.reference_3 IN ('jai_rcv_rcv_rtv_pkg.post_entries', 'jai_rcv_rcv_rtv_pkg.regime_tax_accounting_interim', 'jai_rcv_excise_processing_pkg.accounting_entries', 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise', 'jai_rcv_deliver_rtr_pkg.expense_accounting', 'ja_in_receive_rtv_pkg.post_entries', 'ja_in_receive_rtv_pkg.regime_tax_accounting_interim', 'ja_in_receipt_cenvat_rg_pkg.accounting_entries', 'ja_in_deliver_rtr_pkg.deliver_rtr_reco_nonexcise', 'ja_in_deliver_rtr_pkg.expense_accounting') OR gjr.reference_3 LIKE 'jai_rcv_deliver_rtr_pkg.deliver_rtr_reco_nonexcise%' OR gjr.reference_3 LIKE 'ja_in_deliver_rtr_pkg.deliver_rtr_reco_nonexcise%') AND gjr.reference_4 = 'rcv_transactions' AND gjr.reference_5 = TO_CHAR (jrt.transaction_id) AND gjr.reference_6 = 'transaction_id' AND gjs.je_source_name IN ('Purchasing', 'Purchasing India') AND (gjc.je_category_name = 'Receiving India' OR jai_drilldown_utils_pkg.get_user_categ_name(gjc.je_category_name) = 'Receiving India') UNION ALL SELECT /*+ ORDERED USE_NL(jrh jrl) USE_NL(jrl jrt) USE_NL(jrt rsl) USE_NL(jrt jrrl) USE_NL(jrt gjr) USE_NL(gjr gjh) USE_NL(gjh gjl) USE_NL(gjh gjs) USE_NL(gjh gjc) USE_NL(gjh glct) INDEX(gjr GL_IMPORT_REFERENCES_N3) */ 7000 application_id, 'PO Receipt' event_type, gjl.description event_description, 'JAI PO REC' trx_class, 'JAI PO REC' trx_class_name, jrt.transaction_type trx_type, jrt.shipment_header_id trx_hdr_id, 'jai_rcv_transactions' trx_hdr_table, jrt.organization_id inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date Currency_conversion_date, gjh.currency_conversion_rate Currency_conversion_rate, gjh.currency_conversion_type Currency_conversion_type, glct.user_conversion_type Currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jrt.last_update_date LAST_UPDATE_DATE, jrt.last_updated_by LAST_UPDATED_BY, jrt.creation_date CREATION_DATE, jrt.created_by CREATED_BY, jrt.last_update_login LAST_UPDATE_LOGIN, rsl.line_num receipt_line_number, jrt.inventory_item_id item_id, NULL uom, jrt.quantity quantity, NULL location, rsl.to_subinventory sub_inventory, NVL (jrh.excise_invoice_no, jrl.excise_invoice_no) excise_inv_num, NVL (jrh.excise_invoice_date, jrl.excise_invoice_date) excise_invoice_date, NVL (jrt.vat_invoice_no, jrrl.invoice_no) vat_inv_num, NVL (jrt.vat_invoice_date, jrrl.invoice_date) vat_invoice_date, NULL party_name, NULL party_number, NULL party_site, jrt.receipt_num receipt_num, jrt.transaction_date receipt_date, jrh.shipment_num shipment_num, jrh.shipped_date shipment_date FROM jai_rcv_headers jrh, jai_rcv_lines jrl, jai_rcv_transactions jrt, rcv_shipment_lines rsl, jai_rcv_rgm_lines jrrl, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl , gl_je_sources gjs, gl_je_categories gjc, gl_daily_conversion_types glct WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND rsl.shipment_header_id = jrt.shipment_header_id AND rsl.shipment_line_id = jrt.shipment_line_id AND jrh.shipment_header_id = jrl.shipment_header_id AND jrl.shipment_header_id = jrt.shipment_header_id AND jrl.shipment_line_id = jrt.shipment_line_id AND jrrl.shipment_header_id(+) = jrt.shipment_header_id AND jrrl.shipment_line_id(+) = jrt.shipment_line_id AND jrh.receipt_source_code = 'CUSTOMER' AND jrh.shipment_header_id = jrt.shipment_header_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 IN ('jai_rgm_claim_pkg.do_rma_accounting', 'jai_rcv_excise_processing_pkg.accounting_entries') AND gjr.reference_4 = 'rcv_transactions' AND gjr.reference_5 IN ('transaction_id', TO_CHAR (jrt.transaction_id)) AND gjr.reference_6 IN (TO_CHAR (jrt.transaction_id), 'transaction_id') AND gjs.je_source_name IN ('Purchasing', 'Purchasing India') AND (gjc.je_category_name = 'Receiving India' OR jai_drilldown_utils_pkg.get_user_categ_name(gjc.je_category_name) = 'Receiving India') UNION ALL SELECT /*+ ORDERED USE_NL(jrh jrl) USE_NL(jrl jrt) USE_NL(jrt rsl) USE_NL(jrt jrrl) USE_NL(jrt jrr) USE_NL(jrt gjr) USE_NL(gjr gjh) USE_NL(gjh gjl) USE_NL(gjh gjs) USE_NL(gjh gjc) USE_NL(gjh glct) INDEX(gjr GL_IMPORT_REFERENCES_N3) */ 7000 application_id, 'PO Receipt' event_type, gjl.description event_description, 'JAI PO REC' trx_class, 'JAI PO REC' trx_class_name, jrt.transaction_type trx_type, jrt.shipment_header_id trx_hdr_id, 'jai_rcv_transactions' trx_hdr_table, jrt.organization_id inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jrt.last_update_date LAST_UPDATE_DATE, jrt.last_updated_by LAST_UPDATED_BY, jrt.creation_date CREATION_DATE, jrt.created_by CREATED_BY, jrt.last_update_login LAST_UPDATE_LOGIN, rsl.line_num receipt_line_number, jrt.inventory_item_id item_id, NULL uom, jrt.quantity quantity, NULL location, rsl.to_subinventory sub_inventory, NVL (jrh.excise_invoice_no, jrl.excise_invoice_no) excise_inv_num, NVL (jrh.excise_invoice_date, jrl.excise_invoice_date) excise_invoice_date, NVL (jrt.vat_invoice_no, jrrl.invoice_no) vat_inv_num, NVL (jrt.vat_invoice_date, jrrl.invoice_date) vat_invoice_date, NULL party_name, NULL party_number, NULL party_site, jrt.receipt_num receipt_num, jrt.transaction_date receipt_date, jrh.shipment_num shipment_num, jrh.shipped_date shipment_date FROM jai_rcv_headers jrh, jai_rcv_lines jrl, jai_rcv_transactions jrt, rcv_shipment_lines rsl, jai_rcv_rgm_lines jrrl, jai_rgm_trx_records jrr, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl , gl_je_sources gjs, gl_je_categories gjc, gl_daily_conversion_types glct WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND jrh.receipt_source_code IN ('VENDOR', 'INTERNAL ORDER') AND rsl.shipment_header_id = jrt.shipment_header_id AND rsl.shipment_line_id = jrt.shipment_line_id AND jrh.shipment_header_id = jrl.shipment_header_id AND jrl.shipment_header_id = jrt.shipment_header_id AND jrl.shipment_line_id = jrt.shipment_line_id AND jrrl.shipment_header_id(+) = jrt.shipment_header_id AND jrrl.shipment_line_id(+) = jrt.shipment_line_id AND jrh.shipment_header_id = jrt.shipment_header_id AND jrt.transaction_id = jrr.source_document_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'jai_rgm_claim_pkg.process_claim' AND gjr.reference_4 = 'jai_rgm_trx_records' AND gjr.reference_5 = 'repository_id' AND gjr.reference_6 = TO_CHAR (jrr.repository_id) AND gjs.je_source_name IN ('Purchasing', 'Purchasing India') AND (gjc.je_category_name = 'Receiving India' OR jai_drilldown_utils_pkg.get_user_categ_name(gjc.je_category_name) = 'Receiving India') UNION ALL SELECT /*+ ORDERED USE_NL(jrh jrl) USE_NL(jrl jrt) USE_NL(jrt rsl) USE_NL(jrt jrrl) USE_NL(jrt jrr) USE_NL(jrt gjr) USE_NL(gjr gjh) USE_NL(gjh gjl) USE_NL(gjh gjs) USE_NL(gjh gjc) USE_NL(gjh glct) INDEX(gjr GL_IMPORT_REFERENCES_N3) */ 7000 application_id, 'PO Receipt' event_type, gjl.description event_description, 'JAI PO REC' trx_class, 'JAI PO REC' trx_class_name, jrt.transaction_type trx_type, jrt.shipment_header_id trx_hdr_id, 'jai_rcv_transactions' trx_hdr_table, jrt.organization_id inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jrt.last_update_date LAST_UPDATE_DATE, jrt.last_updated_by LAST_UPDATED_BY, jrt.creation_date CREATION_DATE, jrt.created_by CREATED_BY, jrt.last_update_login LAST_UPDATE_LOGIN, rsl.line_num receipt_line_number, jrt.inventory_item_id item_id, NULL uom, jrt.quantity quantity, NULL location, rsl.to_subinventory sub_inventory, NVL (jrh.excise_invoice_no, jrl.excise_invoice_no) excise_inv_num, NVL (jrh.excise_invoice_date, jrl.excise_invoice_date) excise_invoice_date, NVL (jrt.vat_invoice_no, jrrl.invoice_no) vat_inv_num, NVL (jrt.vat_invoice_date, jrrl.invoice_date) vat_invoice_date, NULL party_name, NULL party_number, NULL party_site, jrt.receipt_num receipt_num, jrt.transaction_date receipt_date, jrh.shipment_num shipment_num, jrh.shipped_date shipment_date FROM jai_rcv_headers jrh, jai_rcv_lines jrl, jai_rcv_transactions jrt, rcv_shipment_lines rsl, jai_rcv_rgm_lines jrrl, jai_rgm_trx_records jrr, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl , gl_je_sources gjs, gl_je_categories gjc, gl_daily_conversion_types glct WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND jrh.receipt_source_code = 'CUSTOMER' AND rsl.shipment_header_id = jrt.shipment_header_id AND rsl.shipment_line_id = jrt.shipment_line_id AND jrh.shipment_header_id = jrl.shipment_header_id AND jrl.shipment_header_id = jrt.shipment_header_id AND jrl.shipment_line_id = jrt.shipment_line_id AND jrrl.shipment_header_id(+) = jrt.shipment_header_id AND jrrl.shipment_line_id(+) = jrt.shipment_line_id AND jrh.shipment_header_id = jrt.shipment_header_id AND jrt.transaction_id = jrr.source_document_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'jai_rgm_claim_pkg.process_claim' AND gjr.reference_4 = 'jai_rgm_trx_records' AND gjr.reference_5 = 'repository_id' AND gjr.reference_6 = TO_CHAR (jrr.repository_id) AND gjs.je_source_name IN ('Purchasing', 'Purchasing India') AND (gjc.je_category_name = 'Receiving India' OR jai_drilldown_utils_pkg.get_user_categ_name(gjc.je_category_name) = 'Receiving India') UNION ALL SELECT /*+ ORDERED USE_NL(jrh jrl) USE_NL(jrl jrt) USE_NL(jrt rsl) USE_NL(jrt jrrl) USE_NL(jrt gjr) USE_NL(gjr gjh) USE_NL(gjh gjl) USE_NL(gjh gjs) USE_NL(gjh gjc) USE_NL(gjh glct) INDEX(gjr GL_IMPORT_REFERENCES_N3) */ 7000 application_id, 'PO Receipt' event_type, gjl.description event_description, 'JAI PO REC' trx_class, 'JAI PO REC' trx_class_name, jrt.transaction_type trx_type, jrt.shipment_header_id trx_hdr_id, 'jai_rcv_transactions' trx_hdr_table, jrt.organization_id inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jrt.last_update_date LAST_UPDATE_DATE, jrt.last_updated_by LAST_UPDATED_BY, jrt.creation_date CREATION_DATE, jrt.created_by CREATED_BY, jrt.last_update_login LAST_UPDATE_LOGIN, rsl.line_num receipt_line_number, jrt.inventory_item_id item_id, NULL uom, jrt.quantity quantity, NULL location, rsl.to_subinventory sub_inventory, NVL (jrh.excise_invoice_no, jrl.excise_invoice_no) excise_inv_num, NVL (jrh.excise_invoice_date, jrl.excise_invoice_date) excise_invoice_date, NVL (jrt.vat_invoice_no, jrrl.invoice_no) vat_inv_num, NVL (jrt.vat_invoice_date, jrrl.invoice_date) vat_invoice_date, NULL party_name, NULL party_number, NULL party_site, jrt.receipt_num receipt_num, jrt.transaction_date receipt_date, jrh.shipment_num shipment_num, jrh.shipped_date shipment_date FROM jai_rcv_headers jrh, jai_rcv_lines jrl, jai_rcv_transactions jrt, rcv_shipment_lines rsl, jai_rcv_rgm_lines jrrl, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl , gl_je_sources gjs, gl_je_categories gjc, gl_daily_conversion_types glct WHERE gjh.je_category = gjc.je_category_name AND gjh.je_source = gjs.je_source_name AND gjh.currency_conversion_type = glct.conversion_type AND jrh.receipt_source_code IN ('VENDOR', 'INTERNAL ORDER') AND rsl.shipment_header_id = jrt.shipment_header_id AND rsl.shipment_line_id = jrt.shipment_line_id AND jrh.shipment_header_id = jrl.shipment_header_id AND jrl.shipment_header_id = jrt.shipment_header_id AND jrl.shipment_line_id = jrt.shipment_line_id AND jrrl.shipment_header_id(+) = jrt.shipment_header_id AND jrrl.shipment_line_id(+) = jrt.shipment_line_id AND jrh.shipment_header_id = jrt.shipment_header_id AND gjl.je_header_id = gjh.je_header_id AND gjr.je_header_id = gjl.je_header_id AND gjr.je_line_num = gjl.je_line_num AND gjr.je_batch_id = gjh.je_batch_id AND gjr.reference_2 = 'India Localization Entry' AND gjr.reference_3 = 'jai_rgm_claim_pkg.process_vat' AND gjr.reference_4 = 'JAI_RCV_TRANSACTIONS' AND gjr.reference_5 = 'transaction_id' AND gjr.reference_6 = TO_CHAR (jrt.transaction_id) AND gjs.je_source_name IN ('Purchasing', 'Purchasing India') AND (gjc.je_category_name = 'Receiving India' OR jai_drilldown_utils_pkg.get_user_categ_name(gjc.je_category_name) = 'Receiving India') UNION ALL SELECT 7000 application_id, 'PO Receipt'event_type, gjl.description event_description, 'JAI PO REC'trx_class, 'JAI PO REC'trx_class_name, jrt.transaction_type trx_type, jrt.shipment_header_id trx_hdr_id, 'jai_rcv_transactions'trx_hdr_table, jrt.organization_id inventory_org_id, gjh.ledger_id set_of_books_id, gjh.je_category Je_Category, gjc.user_je_category_name User_Je_Category_Name, gjh.je_source Je_Source, gjs.user_je_source_name User_Je_Source_Name, gjl.je_header_id JE_Header_Id, gjl.je_line_num JE_Line_Num, gjh.currency_code Entered_Currency, gjl.entered_dr Entered_Dr, gjl.entered_cr Entered_Cr, gjl.accounted_dr Accounted_Dr, gjl.accounted_cr Accounted_Cr, gjh.currency_conversion_date currency_conversion_date, gjh.currency_conversion_rate currency_conversion_rate, gjh.currency_conversion_type currency_conversion_type, glct.user_conversion_type currency_user_conversion_type, gjl.code_combination_id Account_Id, gjl.effective_date Accounting_Date, jrt.last_update_date LAST_UPDATE_DATE, jrt.last_updated_by LAST_UPDATED_BY, jrt.creation_date CREATION_DATE, jrt.created_by CREATED_BY, jrt.last_update_login LAST_UPDATE_LOGIN, rsl.line_num receipt_line_number, jrt.inventory_item_id item_id, uom.unit_of_measure uom, jrt.quantity quantity, hr.description location, rsl.to_subinventory sub_inventory, NVL(jrh.excise_invoice_no,jrl.excise_invoice_no)excise_inv_num, NVL(jrh.excise_invoice_date,jrl.excise_invoice_date)excise_invoice_date, NVL(jrt.vat_invoice_no,jrrl.invoice_no)vat_inv_num, NVL(jrt.vat_invoice_date,jrrl.invoice_date)vat_invoice_date, pv.vendor_name party_name, pv.segment1 party_number, pvs.vendor_site_code party_site, jrt.receipt_num receipt_num, jrt.transaction_date receipt_date, jrh.shipment_num shipment_num, jrh.shipped_date shipment_date FROM rcv_shipment_lines rsl, po_vendors pv, po_vendor_sites_all pvs, hr_locations hr, mtl_units_of_measure_vl uom, mtl_material_transactions mmt, jai_rcv_lines jrl, jai_rcv_rgm_lines jrrl, jai_rcv_transactions jrt, jai_rcv_headers jrh, gl_daily_conversion_types glct, gl_je_categories gjc, gl_je_sources gjs, GL_IMPORT_REFERENCES gjr, GL_JE_HEADERS gjh, GL_JE_LINES gjl WHERE gjh.je_category=gjc.je_category_name AND gjh.je_source=gjs.je_source_name AND gjh.currency_conversion_type=glct.conversion_type AND jrh.receipt_source_code IN ('VENDOR', 'INTERNAL ORDER') AND jrh.vendor_id=pv.vendor_id AND jrh.vendor_site_id=pvs.vendor_site_id(+) AND jrt.location_id=hr.location_id(+) AND jrt.uom_code=uom.uom_code AND rsl.shipment_header_id=jrt.shipment_header_id AND rsl.shipment_line_id=jrt.shipment_line_id AND jrl.shipment_header_id=jrt.shipment_header_id AND jrl.shipment_line_id=jrt.shipment_line_id AND jrrl.shipment_header_id(+)=jrt.shipment_header_id AND jrrl.shipment_line_id(+)=jrt.shipment_line_id AND jrh.shipment_header_id=jrt.shipment_header_id AND gjl.je_header_id=gjh.je_header_id AND gjr.je_header_id=gjl.je_header_id AND gjr.je_line_num=gjl.je_line_num AND gjr.je_batch_id=gjh.je_batch_id AND gjr.reference_2='India Localization Entry' AND gjr.reference_3='jai_rcv_deliver_rtr_pkg.standard_costing' AND gjr.reference_4='rcv_transactions' AND gjr.reference_5=TO_CHAR(jrt.transaction_id) AND gjr.reference_6=TO_CHAR(mmt.transaction_id) AND gjs.je_source_name='Inventory India' AND (gjc.je_category_name = 'MTL' OR jai_drilldown_utils_pkg.get_user_categ_name(gjc.je_category_name) = 'MTL') AND mmt.transaction_type_id = 80 AND mmt.transaction_source_type_id = 13 AND mmt.transaction_action_id = 24 AND mmt.transaction_reference = TO_CHAR(jrt.transaction_id)

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(JRH JRL) USE_NL(JRL JRT) USE_NL(JRT RSL) USE_NL(JRT JRRL) USE_NL(JRT GJR) USE_NL(GJR GJH) USE_NL(GJH GJL) USE_NL(GJH GJS) USE_NL(GJH GJC) USE_NL(GJH GLCT) INDEX(GJR GL_IMPORT_REFERENCES_N3) */ 7000 APPLICATION_ID
, 'PO RECEIPT' EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC' TRX_CLASS
, 'JAI PO REC' TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS' TRX_HDR_TABLE
, JRT.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, NULL UOM
, JRT.QUANTITY QUANTITY
, NULL LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, NVL (JRH.EXCISE_INVOICE_NO
, JRL.EXCISE_INVOICE_NO) EXCISE_INV_NUM
, NVL (JRH.EXCISE_INVOICE_DATE
, JRL.EXCISE_INVOICE_DATE) EXCISE_INVOICE_DATE
, NVL (JRT.VAT_INVOICE_NO
, JRRL.INVOICE_NO) VAT_INV_NUM
, NVL (JRT.VAT_INVOICE_DATE
, JRRL.INVOICE_DATE) VAT_INVOICE_DATE
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, NULL PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM JAI_RCV_HEADERS JRH
, JAI_RCV_LINES JRL
, JAI_RCV_TRANSACTIONS JRT
, RCV_SHIPMENT_LINES RSL
, JAI_RCV_RGM_LINES JRRL
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND RSL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRH.SHIPMENT_HEADER_ID = JRL.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRRL.SHIPMENT_HEADER_ID(+) = JRT.SHIPMENT_HEADER_ID
AND JRRL.SHIPMENT_LINE_ID(+) = JRT.SHIPMENT_LINE_ID
AND JRH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND JRH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND (GJR.REFERENCE_3 IN ('JAI_RCV_RCV_RTV_PKG.POST_ENTRIES'
, 'JAI_RCV_RCV_RTV_PKG.REGIME_TAX_ACCOUNTING_INTERIM'
, 'JAI_RCV_EXCISE_PROCESSING_PKG.ACCOUNTING_ENTRIES'
, 'JAI_RCV_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE'
, 'JAI_RCV_DELIVER_RTR_PKG.EXPENSE_ACCOUNTING'
, 'JA_IN_RECEIVE_RTV_PKG.POST_ENTRIES'
, 'JA_IN_RECEIVE_RTV_PKG.REGIME_TAX_ACCOUNTING_INTERIM'
, 'JA_IN_RECEIPT_CENVAT_RG_PKG.ACCOUNTING_ENTRIES'
, 'JA_IN_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE'
, 'JA_IN_DELIVER_RTR_PKG.EXPENSE_ACCOUNTING') OR GJR.REFERENCE_3 LIKE 'JAI_RCV_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE%' OR GJR.REFERENCE_3 LIKE 'JA_IN_DELIVER_RTR_PKG.DELIVER_RTR_RECO_NONEXCISE%')
AND GJR.REFERENCE_4 = 'RCV_TRANSACTIONS'
AND GJR.REFERENCE_5 = TO_CHAR (JRT.TRANSACTION_ID)
AND GJR.REFERENCE_6 = 'TRANSACTION_ID'
AND GJS.JE_SOURCE_NAME IN ('PURCHASING'
, 'PURCHASING INDIA')
AND (GJC.JE_CATEGORY_NAME = 'RECEIVING INDIA' OR JAI_DRILLDOWN_UTILS_PKG.GET_USER_CATEG_NAME(GJC.JE_CATEGORY_NAME) = 'RECEIVING INDIA') UNION ALL SELECT /*+ ORDERED USE_NL(JRH JRL) USE_NL(JRL JRT) USE_NL(JRT RSL) USE_NL(JRT JRRL) USE_NL(JRT GJR) USE_NL(GJR GJH) USE_NL(GJH GJL) USE_NL(GJH GJS) USE_NL(GJH GJC) USE_NL(GJH GLCT) INDEX(GJR GL_IMPORT_REFERENCES_N3) */ 7000 APPLICATION_ID
, 'PO RECEIPT' EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC' TRX_CLASS
, 'JAI PO REC' TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS' TRX_HDR_TABLE
, JRT.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, NULL UOM
, JRT.QUANTITY QUANTITY
, NULL LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, NVL (JRH.EXCISE_INVOICE_NO
, JRL.EXCISE_INVOICE_NO) EXCISE_INV_NUM
, NVL (JRH.EXCISE_INVOICE_DATE
, JRL.EXCISE_INVOICE_DATE) EXCISE_INVOICE_DATE
, NVL (JRT.VAT_INVOICE_NO
, JRRL.INVOICE_NO) VAT_INV_NUM
, NVL (JRT.VAT_INVOICE_DATE
, JRRL.INVOICE_DATE) VAT_INVOICE_DATE
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, NULL PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM JAI_RCV_HEADERS JRH
, JAI_RCV_LINES JRL
, JAI_RCV_TRANSACTIONS JRT
, RCV_SHIPMENT_LINES RSL
, JAI_RCV_RGM_LINES JRRL
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND RSL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRL.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRRL.SHIPMENT_HEADER_ID(+) = JRT.SHIPMENT_HEADER_ID
AND JRRL.SHIPMENT_LINE_ID(+) = JRT.SHIPMENT_LINE_ID
AND JRH.RECEIPT_SOURCE_CODE = 'CUSTOMER'
AND JRH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 IN ('JAI_RGM_CLAIM_PKG.DO_RMA_ACCOUNTING'
, 'JAI_RCV_EXCISE_PROCESSING_PKG.ACCOUNTING_ENTRIES')
AND GJR.REFERENCE_4 = 'RCV_TRANSACTIONS'
AND GJR.REFERENCE_5 IN ('TRANSACTION_ID'
, TO_CHAR (JRT.TRANSACTION_ID))
AND GJR.REFERENCE_6 IN (TO_CHAR (JRT.TRANSACTION_ID)
, 'TRANSACTION_ID')
AND GJS.JE_SOURCE_NAME IN ('PURCHASING'
, 'PURCHASING INDIA')
AND (GJC.JE_CATEGORY_NAME = 'RECEIVING INDIA' OR JAI_DRILLDOWN_UTILS_PKG.GET_USER_CATEG_NAME(GJC.JE_CATEGORY_NAME) = 'RECEIVING INDIA') UNION ALL SELECT /*+ ORDERED USE_NL(JRH JRL) USE_NL(JRL JRT) USE_NL(JRT RSL) USE_NL(JRT JRRL) USE_NL(JRT JRR) USE_NL(JRT GJR) USE_NL(GJR GJH) USE_NL(GJH GJL) USE_NL(GJH GJS) USE_NL(GJH GJC) USE_NL(GJH GLCT) INDEX(GJR GL_IMPORT_REFERENCES_N3) */ 7000 APPLICATION_ID
, 'PO RECEIPT' EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC' TRX_CLASS
, 'JAI PO REC' TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS' TRX_HDR_TABLE
, JRT.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, NULL UOM
, JRT.QUANTITY QUANTITY
, NULL LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, NVL (JRH.EXCISE_INVOICE_NO
, JRL.EXCISE_INVOICE_NO) EXCISE_INV_NUM
, NVL (JRH.EXCISE_INVOICE_DATE
, JRL.EXCISE_INVOICE_DATE) EXCISE_INVOICE_DATE
, NVL (JRT.VAT_INVOICE_NO
, JRRL.INVOICE_NO) VAT_INV_NUM
, NVL (JRT.VAT_INVOICE_DATE
, JRRL.INVOICE_DATE) VAT_INVOICE_DATE
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, NULL PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM JAI_RCV_HEADERS JRH
, JAI_RCV_LINES JRL
, JAI_RCV_TRANSACTIONS JRT
, RCV_SHIPMENT_LINES RSL
, JAI_RCV_RGM_LINES JRRL
, JAI_RGM_TRX_RECORDS JRR
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND JRH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND RSL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRL.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRRL.SHIPMENT_HEADER_ID(+) = JRT.SHIPMENT_HEADER_ID
AND JRRL.SHIPMENT_LINE_ID(+) = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRT.TRANSACTION_ID = JRR.SOURCE_DOCUMENT_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAI_RGM_CLAIM_PKG.PROCESS_CLAIM'
AND GJR.REFERENCE_4 = 'JAI_RGM_TRX_RECORDS'
AND GJR.REFERENCE_5 = 'REPOSITORY_ID'
AND GJR.REFERENCE_6 = TO_CHAR (JRR.REPOSITORY_ID)
AND GJS.JE_SOURCE_NAME IN ('PURCHASING'
, 'PURCHASING INDIA')
AND (GJC.JE_CATEGORY_NAME = 'RECEIVING INDIA' OR JAI_DRILLDOWN_UTILS_PKG.GET_USER_CATEG_NAME(GJC.JE_CATEGORY_NAME) = 'RECEIVING INDIA') UNION ALL SELECT /*+ ORDERED USE_NL(JRH JRL) USE_NL(JRL JRT) USE_NL(JRT RSL) USE_NL(JRT JRRL) USE_NL(JRT JRR) USE_NL(JRT GJR) USE_NL(GJR GJH) USE_NL(GJH GJL) USE_NL(GJH GJS) USE_NL(GJH GJC) USE_NL(GJH GLCT) INDEX(GJR GL_IMPORT_REFERENCES_N3) */ 7000 APPLICATION_ID
, 'PO RECEIPT' EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC' TRX_CLASS
, 'JAI PO REC' TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS' TRX_HDR_TABLE
, JRT.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, NULL UOM
, JRT.QUANTITY QUANTITY
, NULL LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, NVL (JRH.EXCISE_INVOICE_NO
, JRL.EXCISE_INVOICE_NO) EXCISE_INV_NUM
, NVL (JRH.EXCISE_INVOICE_DATE
, JRL.EXCISE_INVOICE_DATE) EXCISE_INVOICE_DATE
, NVL (JRT.VAT_INVOICE_NO
, JRRL.INVOICE_NO) VAT_INV_NUM
, NVL (JRT.VAT_INVOICE_DATE
, JRRL.INVOICE_DATE) VAT_INVOICE_DATE
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, NULL PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM JAI_RCV_HEADERS JRH
, JAI_RCV_LINES JRL
, JAI_RCV_TRANSACTIONS JRT
, RCV_SHIPMENT_LINES RSL
, JAI_RCV_RGM_LINES JRRL
, JAI_RGM_TRX_RECORDS JRR
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND JRH.RECEIPT_SOURCE_CODE = 'CUSTOMER'
AND RSL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRL.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRRL.SHIPMENT_HEADER_ID(+) = JRT.SHIPMENT_HEADER_ID
AND JRRL.SHIPMENT_LINE_ID(+) = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRT.TRANSACTION_ID = JRR.SOURCE_DOCUMENT_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAI_RGM_CLAIM_PKG.PROCESS_CLAIM'
AND GJR.REFERENCE_4 = 'JAI_RGM_TRX_RECORDS'
AND GJR.REFERENCE_5 = 'REPOSITORY_ID'
AND GJR.REFERENCE_6 = TO_CHAR (JRR.REPOSITORY_ID)
AND GJS.JE_SOURCE_NAME IN ('PURCHASING'
, 'PURCHASING INDIA')
AND (GJC.JE_CATEGORY_NAME = 'RECEIVING INDIA' OR JAI_DRILLDOWN_UTILS_PKG.GET_USER_CATEG_NAME(GJC.JE_CATEGORY_NAME) = 'RECEIVING INDIA') UNION ALL SELECT /*+ ORDERED USE_NL(JRH JRL) USE_NL(JRL JRT) USE_NL(JRT RSL) USE_NL(JRT JRRL) USE_NL(JRT GJR) USE_NL(GJR GJH) USE_NL(GJH GJL) USE_NL(GJH GJS) USE_NL(GJH GJC) USE_NL(GJH GLCT) INDEX(GJR GL_IMPORT_REFERENCES_N3) */ 7000 APPLICATION_ID
, 'PO RECEIPT' EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC' TRX_CLASS
, 'JAI PO REC' TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS' TRX_HDR_TABLE
, JRT.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, NULL UOM
, JRT.QUANTITY QUANTITY
, NULL LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, NVL (JRH.EXCISE_INVOICE_NO
, JRL.EXCISE_INVOICE_NO) EXCISE_INV_NUM
, NVL (JRH.EXCISE_INVOICE_DATE
, JRL.EXCISE_INVOICE_DATE) EXCISE_INVOICE_DATE
, NVL (JRT.VAT_INVOICE_NO
, JRRL.INVOICE_NO) VAT_INV_NUM
, NVL (JRT.VAT_INVOICE_DATE
, JRRL.INVOICE_DATE) VAT_INVOICE_DATE
, NULL PARTY_NAME
, NULL PARTY_NUMBER
, NULL PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM JAI_RCV_HEADERS JRH
, JAI_RCV_LINES JRL
, JAI_RCV_TRANSACTIONS JRT
, RCV_SHIPMENT_LINES RSL
, JAI_RCV_RGM_LINES JRRL
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
, GL_JE_SOURCES GJS
, GL_JE_CATEGORIES GJC
, GL_DAILY_CONVERSION_TYPES GLCT
WHERE GJH.JE_CATEGORY = GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE = GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE = GLCT.CONVERSION_TYPE
AND JRH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND RSL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRL.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_LINE_ID = JRT.SHIPMENT_LINE_ID
AND JRRL.SHIPMENT_HEADER_ID(+) = JRT.SHIPMENT_HEADER_ID
AND JRRL.SHIPMENT_LINE_ID(+) = JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID = JRT.SHIPMENT_HEADER_ID
AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM = GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID = GJH.JE_BATCH_ID
AND GJR.REFERENCE_2 = 'INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3 = 'JAI_RGM_CLAIM_PKG.PROCESS_VAT'
AND GJR.REFERENCE_4 = 'JAI_RCV_TRANSACTIONS'
AND GJR.REFERENCE_5 = 'TRANSACTION_ID'
AND GJR.REFERENCE_6 = TO_CHAR (JRT.TRANSACTION_ID)
AND GJS.JE_SOURCE_NAME IN ('PURCHASING'
, 'PURCHASING INDIA')
AND (GJC.JE_CATEGORY_NAME = 'RECEIVING INDIA' OR JAI_DRILLDOWN_UTILS_PKG.GET_USER_CATEG_NAME(GJC.JE_CATEGORY_NAME) = 'RECEIVING INDIA') UNION ALL SELECT 7000 APPLICATION_ID
, 'PO RECEIPT'EVENT_TYPE
, GJL.DESCRIPTION EVENT_DESCRIPTION
, 'JAI PO REC'TRX_CLASS
, 'JAI PO REC'TRX_CLASS_NAME
, JRT.TRANSACTION_TYPE TRX_TYPE
, JRT.SHIPMENT_HEADER_ID TRX_HDR_ID
, 'JAI_RCV_TRANSACTIONS'TRX_HDR_TABLE
, JRT.ORGANIZATION_ID INVENTORY_ORG_ID
, GJH.LEDGER_ID SET_OF_BOOKS_ID
, GJH.JE_CATEGORY JE_CATEGORY
, GJC.USER_JE_CATEGORY_NAME USER_JE_CATEGORY_NAME
, GJH.JE_SOURCE JE_SOURCE
, GJS.USER_JE_SOURCE_NAME USER_JE_SOURCE_NAME
, GJL.JE_HEADER_ID JE_HEADER_ID
, GJL.JE_LINE_NUM JE_LINE_NUM
, GJH.CURRENCY_CODE ENTERED_CURRENCY
, GJL.ENTERED_DR ENTERED_DR
, GJL.ENTERED_CR ENTERED_CR
, GJL.ACCOUNTED_DR ACCOUNTED_DR
, GJL.ACCOUNTED_CR ACCOUNTED_CR
, GJH.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, GJH.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, GJH.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE
, GLCT.USER_CONVERSION_TYPE CURRENCY_USER_CONVERSION_TYPE
, GJL.CODE_COMBINATION_ID ACCOUNT_ID
, GJL.EFFECTIVE_DATE ACCOUNTING_DATE
, JRT.LAST_UPDATE_DATE LAST_UPDATE_DATE
, JRT.LAST_UPDATED_BY LAST_UPDATED_BY
, JRT.CREATION_DATE CREATION_DATE
, JRT.CREATED_BY CREATED_BY
, JRT.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, RSL.LINE_NUM RECEIPT_LINE_NUMBER
, JRT.INVENTORY_ITEM_ID ITEM_ID
, UOM.UNIT_OF_MEASURE UOM
, JRT.QUANTITY QUANTITY
, HR.DESCRIPTION LOCATION
, RSL.TO_SUBINVENTORY SUB_INVENTORY
, NVL(JRH.EXCISE_INVOICE_NO
, JRL.EXCISE_INVOICE_NO)EXCISE_INV_NUM
, NVL(JRH.EXCISE_INVOICE_DATE
, JRL.EXCISE_INVOICE_DATE)EXCISE_INVOICE_DATE
, NVL(JRT.VAT_INVOICE_NO
, JRRL.INVOICE_NO)VAT_INV_NUM
, NVL(JRT.VAT_INVOICE_DATE
, JRRL.INVOICE_DATE)VAT_INVOICE_DATE
, PV.VENDOR_NAME PARTY_NAME
, PV.SEGMENT1 PARTY_NUMBER
, PVS.VENDOR_SITE_CODE PARTY_SITE
, JRT.RECEIPT_NUM RECEIPT_NUM
, JRT.TRANSACTION_DATE RECEIPT_DATE
, JRH.SHIPMENT_NUM SHIPMENT_NUM
, JRH.SHIPPED_DATE SHIPMENT_DATE
FROM RCV_SHIPMENT_LINES RSL
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS HR
, MTL_UNITS_OF_MEASURE_VL UOM
, MTL_MATERIAL_TRANSACTIONS MMT
, JAI_RCV_LINES JRL
, JAI_RCV_RGM_LINES JRRL
, JAI_RCV_TRANSACTIONS JRT
, JAI_RCV_HEADERS JRH
, GL_DAILY_CONVERSION_TYPES GLCT
, GL_JE_CATEGORIES GJC
, GL_JE_SOURCES GJS
, GL_IMPORT_REFERENCES GJR
, GL_JE_HEADERS GJH
, GL_JE_LINES GJL
WHERE GJH.JE_CATEGORY=GJC.JE_CATEGORY_NAME
AND GJH.JE_SOURCE=GJS.JE_SOURCE_NAME
AND GJH.CURRENCY_CONVERSION_TYPE=GLCT.CONVERSION_TYPE
AND JRH.RECEIPT_SOURCE_CODE IN ('VENDOR'
, 'INTERNAL ORDER')
AND JRH.VENDOR_ID=PV.VENDOR_ID
AND JRH.VENDOR_SITE_ID=PVS.VENDOR_SITE_ID(+)
AND JRT.LOCATION_ID=HR.LOCATION_ID(+)
AND JRT.UOM_CODE=UOM.UOM_CODE
AND RSL.SHIPMENT_HEADER_ID=JRT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID=JRT.SHIPMENT_LINE_ID
AND JRL.SHIPMENT_HEADER_ID=JRT.SHIPMENT_HEADER_ID
AND JRL.SHIPMENT_LINE_ID=JRT.SHIPMENT_LINE_ID
AND JRRL.SHIPMENT_HEADER_ID(+)=JRT.SHIPMENT_HEADER_ID
AND JRRL.SHIPMENT_LINE_ID(+)=JRT.SHIPMENT_LINE_ID
AND JRH.SHIPMENT_HEADER_ID=JRT.SHIPMENT_HEADER_ID
AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
AND GJR.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJR.JE_LINE_NUM=GJL.JE_LINE_NUM
AND GJR.JE_BATCH_ID=GJH.JE_BATCH_ID
AND GJR.REFERENCE_2='INDIA LOCALIZATION ENTRY'
AND GJR.REFERENCE_3='JAI_RCV_DELIVER_RTR_PKG.STANDARD_COSTING'
AND GJR.REFERENCE_4='RCV_TRANSACTIONS'
AND GJR.REFERENCE_5=TO_CHAR(JRT.TRANSACTION_ID)
AND GJR.REFERENCE_6=TO_CHAR(MMT.TRANSACTION_ID)
AND GJS.JE_SOURCE_NAME='INVENTORY INDIA'
AND (GJC.JE_CATEGORY_NAME = 'MTL' OR JAI_DRILLDOWN_UTILS_PKG.GET_USER_CATEG_NAME(GJC.JE_CATEGORY_NAME) = 'MTL')
AND MMT.TRANSACTION_TYPE_ID = 80
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 13
AND MMT.TRANSACTION_ACTION_ID = 24
AND MMT.TRANSACTION_REFERENCE = TO_CHAR(JRT.TRANSACTION_ID)