JA India - Return to Vendor - draft
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Return to Vendor Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINRECI_XML
DB package: JA_JAINRECI_XMLP_PKG
Source: India - Return to Vendor Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINRECI_XML
DB package: JA_JAINRECI_XMLP_PKG
Run
JA India - Return to Vendor - draft and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT min(rg.register_id) register_id , jrcv.location_id, jrcv.inventory_item_id, max(rg.division_no) division_no, max(rg.range_no) range_no, rg.excise_invoice_no, rg.excise_invoice_date, rg.register_type, min(rg.slno) slno, sum(rg.dr_basic_ed) dr_basic_ed, sum(rg.dr_additional_ed) dr_additional_ed, sum(rg.dr_additional_cvd) dr_additional_cvd , sum(rg.dr_other_ed) dr_other_ed, hou.excise_duty_region, hou.excise_duty_zone, hou.excise_duty_reg_no, hou.excise_duty_circle, hou.excise_duty_comm, hou.ec_code, hou.st_reg_no, hou.cst_reg_no, rcv.organization_id, rcv.transaction_id, rcv.shipment_line_id, rcv.parent_transaction_id, rcv.rma_reference, rcv.po_unit_price, rcv.primary_quantity, (rcv.primary_quantity * rcv.po_unit_price) line_amount, msi.primary_uom_code, msi.description, po.vendor_name, min(rounding_id) rounding_id, hou.vat_reg_no, JA_JAINRECI_XMLP_PKG.cf_organization_nameformula(rcv.organization_id) CF_ORGANIZATION_NAME, JA_JAINRECI_XMLP_PKG.cf_currency_codeformula(rcv.organization_id) CF_CURRENCY_CODE, JA_JAINRECI_XMLP_PKG.cf_additional_edformula(sum ( rg.dr_additional_ed )) CF_additional_ed, JA_JAINRECI_XMLP_PKG.cf_additional_cvdformula(sum ( rg.dr_additional_cvd )) CF_additional_cvd, JA_JAINRECI_XMLP_PKG.cf_basic_edformula(sum ( rg.dr_basic_ed )) CF_basic_ed, JA_JAINRECI_XMLP_PKG.cf_other_edformula(sum ( rg.dr_other_ed )) CF_other_ed, JA_JAINRECI_XMLP_PKG.cf_8formula(rcv.organization_id, jrcv.inventory_item_id) CF_8, JA_JAINRECI_XMLP_PKG.cf_rounding_amtsformula(min ( rounding_id ), rg.register_type, sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_ROUNDING_AMTS, JA_JAINRECI_XMLP_PKG.cf_7formula(jrcv.location_id) CF_7, JA_JAINRECI_XMLP_PKG.cf_6formula(jrcv.location_id) CF_6, JA_JAINRECI_XMLP_PKG.cf_5formula(jrcv.location_id) CF_5, JA_JAINRECI_XMLP_PKG.cf_4formula(jrcv.location_id) CF_4, JA_JAINRECI_XMLP_PKG.cf_3formula(jrcv.location_id) CF_3, JA_JAINRECI_XMLP_PKG.cf_2formula(rcv.parent_transaction_id, rcv.shipment_line_id, :CF_CURRENCY_CODE, rcv.primary_quantity) CF_2, JA_JAINRECI_XMLP_PKG.cf_transactional_currformula(rcv.parent_transaction_id) CF_1, JA_JAINRECI_XMLP_PKG.cf_sh_cess_amountformula(rcv.transaction_id, rcv.organization_id, jrcv.location_id, rg.register_type, sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_sh_cess_amount, JA_JAINRECI_XMLP_PKG.cf_cess_amountformula(rcv.transaction_id, rcv.organization_id, jrcv.location_id, rg.register_type, sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_cess_amount, JA_JAINRECI_XMLP_PKG.cf_calc_vat_invnum_dateformula(rcv.transaction_id) CF_CALC_VAT_INVNUM_DATE, JA_JAINRECI_XMLP_PKG.CP_round_basic_ed_p CP_round_basic_ed, JA_JAINRECI_XMLP_PKG.CP_round_additional_ed_p CP_round_additional_ed, JA_JAINRECI_XMLP_PKG.CP_round_additional_cvd_p CP_round_additional_cvd, JA_JAINRECI_XMLP_PKG.CP_round_other_ed_p CP_round_other_ed, JA_JAINRECI_XMLP_PKG.CP_round_cess_p CP_round_cess, JA_JAINRECI_XMLP_PKG.CP_VAT_TAX_p CP_VAT_TAX, JA_JAINRECI_XMLP_PKG.CP_sh_round_cess_p CP_sh_round_cess, JA_JAINRECI_XMLP_PKG.CP_VAT_INVOICE_NUMBER_p CP_VAT_INVOICE_NUMBER, JA_JAINRECI_XMLP_PKG.CP_VAT_INVOICE_DATE_p CP_VAT_INVOICE_DATE FROM rcv_transactions rcv, JAI_CMN_RG_23AC_II_TRXS rg, JAI_CMN_INVENTORY_ORGS hou, mtl_system_items msi, po_vendors po , JAI_RCV_TRANSACTIONS jrcv WHERE rcv.transaction_id = :P_TRANSACTION_ID AND rcv.transaction_type = 'RETURN TO VENDOR' AND rg.organization_id = rcv.organization_id AND jrcv.organization_id = msi.organization_id AND rg.organization_id(+) = RCV.organization_id AND hou.organization_id = rg.organization_id AND jrcv.transaction_id = rcv.transaction_id AND rg.receipt_ref(+) = rcv.transaction_id AND po.vendor_id = nvl(rg.vendor_id, rcv.vendor_id) AND msi.inventory_item_id = jrcv.inventory_item_id AND hou.location_id = jrcv.location_id GROUP BY jrcv.location_id, jrcv.inventory_item_id, rg.excise_invoice_no, rg.excise_invoice_date, rg.register_type, hou.excise_duty_region, hou.excise_duty_zone, hou.excise_duty_reg_no, hou.excise_duty_circle, hou.excise_duty_comm, hou.ec_code, hou.st_reg_no, hou.cst_reg_no, rcv.organization_id, rcv.transaction_id, rcv.shipment_line_id, rcv.parent_transaction_id, rcv.rma_reference, rcv.po_unit_price, rcv.primary_quantity, (rcv.primary_quantity * rcv.po_unit_price), msi.primary_uom_code, msi.description, po.vendor_name, hou.vat_reg_no UNION SELECT min(rg.register_id) register_id , jrcv.location_id, jrcv.inventory_item_id, max(rg.division_no) division_no, max(rg.range_no) range_no, rg.excise_invoice_no, rg.excise_invoice_date, rg.register_type, min(rg.slno) slno, sum(rg.dr_basic_ed) dr_basic_ed, sum(rg.dr_additional_ed) dr_additional_ed, sum(rg.dr_additional_cvd) dr_additional_cvd , sum(rg.dr_other_ed) dr_other_ed, hou.excise_duty_region, hou.excise_duty_zone, hou.excise_duty_reg_no, hou.excise_duty_circle, hou.excise_duty_comm, hou.ec_code, hou.st_reg_no, hou.cst_reg_no, rcv.organization_id, rcv.transaction_id, rcv.shipment_line_id, rcv.parent_transaction_id, rcv.rma_reference, rcv.po_unit_price, rcv.primary_quantity, (rcv.primary_quantity * rcv.po_unit_price) line_amount, msi.primary_uom_code, msi.description, po.vendor_name, min(rounding_id) rounding_id, hou.vat_reg_no, JA_JAINRECI_XMLP_PKG.cf_organization_nameformula(rcv.organization_id) CF_ORGANIZATION_NAME, JA_JAINRECI_XMLP_PKG.cf_currency_codeformula(rcv.organization_id) CF_CURRENCY_CODE, JA_JAINRECI_XMLP_PKG.cf_additional_edformula(sum ( rg.dr_additional_ed )) CF_additional_ed, JA_JAINRECI_XMLP_PKG.cf_additional_cvdformula(sum ( rg.dr_additional_cvd )) CF_additional_cvd, JA_JAINRECI_XMLP_PKG.cf_basic_edformula(sum ( rg.dr_basic_ed )) CF_basic_ed, JA_JAINRECI_XMLP_PKG.cf_other_edformula(sum ( rg.dr_other_ed )) CF_other_ed, JA_JAINRECI_XMLP_PKG.cf_8formula(rcv.organization_id, jrcv.inventory_item_id) CF_8, JA_JAINRECI_XMLP_PKG.cf_rounding_amtsformula(min ( rounding_id ), rg.register_type, sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_ROUNDING_AMTS, JA_JAINRECI_XMLP_PKG.cf_7formula(jrcv.location_id) CF_7, JA_JAINRECI_XMLP_PKG.cf_6formula(jrcv.location_id) CF_6, JA_JAINRECI_XMLP_PKG.cf_5formula(jrcv.location_id) CF_5, JA_JAINRECI_XMLP_PKG.cf_4formula(jrcv.location_id) CF_4, JA_JAINRECI_XMLP_PKG.cf_3formula(jrcv.location_id) CF_3, JA_JAINRECI_XMLP_PKG.cf_2formula(rcv.parent_transaction_id, rcv.shipment_line_id, :CF_CURRENCY_CODE, rcv.primary_quantity) CF_2, JA_JAINRECI_XMLP_PKG.cf_transactional_currformula(rcv.parent_transaction_id) CF_1, JA_JAINRECI_XMLP_PKG.cf_sh_cess_amountformula(rcv.transaction_id, rcv.organization_id, jrcv.location_id, rg.register_type, sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_sh_cess_amount, JA_JAINRECI_XMLP_PKG.cf_cess_amountformula(rcv.transaction_id, rcv.organization_id, jrcv.location_id, rg.register_type, sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_cess_amount, JA_JAINRECI_XMLP_PKG.cf_calc_vat_invnum_dateformula(rcv.transaction_id) CF_CALC_VAT_INVNUM_DATE, JA_JAINRECI_XMLP_PKG.CP_round_basic_ed_p CP_round_basic_ed, JA_JAINRECI_XMLP_PKG.CP_round_additional_ed_p CP_round_additional_ed, JA_JAINRECI_XMLP_PKG.CP_round_additional_cvd_p CP_round_additional_cvd, JA_JAINRECI_XMLP_PKG.CP_round_other_ed_p CP_round_other_ed, JA_JAINRECI_XMLP_PKG.CP_round_cess_p CP_round_cess, JA_JAINRECI_XMLP_PKG.CP_VAT_TAX_p CP_VAT_TAX, JA_JAINRECI_XMLP_PKG.CP_sh_round_cess_p CP_sh_round_cess, JA_JAINRECI_XMLP_PKG.CP_VAT_INVOICE_NUMBER_p CP_VAT_INVOICE_NUMBER, JA_JAINRECI_XMLP_PKG.CP_VAT_INVOICE_DATE_p CP_VAT_INVOICE_DATE FROM rcv_transactions rcv, JAI_CMN_RG_23AC_II_TRXS rg, JAI_CMN_INVENTORY_ORGS hou, mtl_system_items msi, po_vendors po , JAI_RCV_TRANSACTIONS jrcv WHERE rcv.transaction_id = :P_TRANSACTION_ID AND rcv.transaction_type = 'RETURN TO VENDOR' AND rg.organization_id = rcv.organization_id AND jrcv.organization_id = msi.organization_id AND rg.organization_id(+) = RCV.organization_id AND hou.organization_id = rg.organization_id AND jrcv.transaction_id = rcv.transaction_id AND rg.receipt_ref(+) = rcv.transaction_id AND po.vendor_id = nvl(rg.vendor_id, rcv.vendor_id) AND msi.inventory_item_id = jrcv.inventory_item_id AND hou.location_id = jrcv.location_id GROUP BY jrcv.location_id, jrcv.inventory_item_id, rg.excise_invoice_no, rg.excise_invoice_date, rg.register_type, hou.excise_duty_region, hou.excise_duty_zone, hou.excise_duty_reg_no, hou.excise_duty_circle, hou.excise_duty_comm, hou.ec_code, hou.st_reg_no, hou.cst_reg_no, rcv.organization_id, rcv.transaction_id, rcv.shipment_line_id, rcv.parent_transaction_id, rcv.rma_reference, rcv.po_unit_price, rcv.primary_quantity, (rcv.primary_quantity * rcv.po_unit_price), msi.primary_uom_code, msi.description, po.vendor_name, hou.vat_reg_no UNION ALL SELECT min(rg.register_id) register_id, min(rg.location_id) location_id, max(rg.inventory_item_id) inventory_item_id, max(rg.division_no) division_no, max(rg.range_no) range_no, rg.dr_invoice_no, rg.dr_invoice_date, 'PLA', min(rg.slno) slno, sum(rg.dr_basic_ed) dr_basic_ed, sum(rg.dr_additional_ed) dr_additional_ed, 0 dr_additional_cvd , sum(rg.dr_other_ed) dr_other_ed, hou.excise_duty_region, hou.excise_duty_zone, hou.excise_duty_reg_no, hou.excise_duty_circle, hou.excise_duty_comm, hou.ec_code, hou.st_reg_no, hou.cst_reg_no, rcv.organization_id, rcv.transaction_id, rcv.shipment_line_id, rcv.parent_transaction_id, rcv.rma_reference, rcv.po_unit_price, rcv.primary_quantity, (rcv.primary_quantity * rcv.po_unit_price) line_amount, msi.primary_uom_code, msi.description, po.vendor_name, min(rounding_id) rounding_id, hou.vat_reg_no , JA_JAINRECI_XMLP_PKG.cf_organization_nameformula(rcv.organization_id) CF_ORGANIZATION_NAME, JA_JAINRECI_XMLP_PKG.cf_currency_codeformula(rcv.organization_id) CF_CURRENCY_CODE, JA_JAINRECI_XMLP_PKG.cf_additional_edformula(sum ( rg.dr_additional_ed )) CF_additional_ed, JA_JAINRECI_XMLP_PKG.cf_additional_cvdformula(0) CF_additional_cvd, JA_JAINRECI_XMLP_PKG.cf_basic_edformula(sum ( rg.dr_basic_ed )) CF_basic_ed, JA_JAINRECI_XMLP_PKG.cf_other_edformula(sum ( rg.dr_other_ed )) CF_other_ed, JA_JAINRECI_XMLP_PKG.cf_8formula(rcv.organization_id, max(rg.inventory_item_id)) CF_8, JA_JAINRECI_XMLP_PKG.cf_rounding_amtsformula(min ( rounding_id ), 'PLA', sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_ROUNDING_AMTS, JA_JAINRECI_XMLP_PKG.cf_7formula(min(rg.location_id)) CF_7, JA_JAINRECI_XMLP_PKG.cf_6formula(min(rg.location_id)) CF_6, JA_JAINRECI_XMLP_PKG.cf_5formula(min(rg.location_id)) CF_5, JA_JAINRECI_XMLP_PKG.cf_4formula(min(rg.location_id)) CF_4, JA_JAINRECI_XMLP_PKG.cf_3formula(min(rg.location_id)) CF_3, JA_JAINRECI_XMLP_PKG.cf_2formula(rcv.parent_transaction_id, rcv.shipment_line_id, JA_JAINRECI_XMLP_PKG.cf_currency_codeformula(rcv.organization_id), rcv.primary_quantity) CF_2, JA_JAINRECI_XMLP_PKG.cf_transactional_currformula(rcv.parent_transaction_id) CF_1, JA_JAINRECI_XMLP_PKG.cf_sh_cess_amountformula(rcv.transaction_id, rcv.organization_id, min(rg.location_id), 'PLA', sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_sh_cess_amount, JA_JAINRECI_XMLP_PKG.cf_cess_amountformula(rcv.transaction_id, rcv.organization_id, min(rg.location_id), 'PLA', sum ( rg.dr_basic_ed ), sum ( rg.dr_additional_ed ), sum ( rg.dr_other_ed )) CF_cess_amount, JA_JAINRECI_XMLP_PKG.cf_calc_vat_invnum_dateformula(rcv.transaction_id) CF_CALC_VAT_INVNUM_DATE, JA_JAINRECI_XMLP_PKG.CP_round_basic_ed_p CP_round_basic_ed, JA_JAINRECI_XMLP_PKG.CP_round_additional_ed_p CP_round_additional_ed, JA_JAINRECI_XMLP_PKG.CP_round_additional_cvd_p CP_round_additional_cvd, JA_JAINRECI_XMLP_PKG.CP_round_other_ed_p CP_round_other_ed, JA_JAINRECI_XMLP_PKG.CP_round_cess_p CP_round_cess, JA_JAINRECI_XMLP_PKG.CP_VAT_TAX_p CP_VAT_TAX, JA_JAINRECI_XMLP_PKG.CP_sh_round_cess_p CP_sh_round_cess, JA_JAINRECI_XMLP_PKG.CP_VAT_INVOICE_NUMBER_p CP_VAT_INVOICE_NUMBER, JA_JAINRECI_XMLP_PKG.CP_VAT_INVOICE_DATE_p CP_VAT_INVOICE_DATE FROM rcv_transactions rcv, JAI_CMN_RG_PLA_TRXS rg, JAI_CMN_INVENTORY_ORGS hou, mtl_system_items msi, po_vendors po WHERE rcv.transaction_id = :P_TRANSACTION_ID AND rcv.transaction_type = 'RETURN TO VENDOR' AND rg.ref_document_id = to_char(rcv.transaction_id) AND rg.organization_id = rcv.organization_id AND po.vendor_id = nvl(rg.vendor_id,rcv.vendor_id) AND RG.TRANSACTION_source_num=19 AND msi.inventory_item_id = rg.inventory_item_id AND msi.organization_id = rg.organization_id AND hou.organization_id = rg.organization_id AND hou.location_id = rg.location_id GROUP BY rg.dr_invoice_no, rg.dr_invoice_date, 'PLA', hou.excise_duty_region, hou.excise_duty_zone, hou.excise_duty_reg_no |