ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F0CF3869E053B46B63588994 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, 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 Y JA JAINRECI_XML US JA India - Return to Vendor - draft 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 ZHS JA 印度 - 向供应商退货报表- 不支持：已保留供将来使用 Application: 亚太地区本地化 Source: 印度 - 向供应商退货报表 (XML) - 不支持：已保留供将来使用 Short Name: JAINRECI_XML DB package: JA_JAINRECI_XMLP_PKG BI Publisher :cf_currency_code :cp_round_additional_cvd :cp_round_additional_ed :cp_round_basic_ed :cp_round_cess :cp_round_other_ed :cp_sh_round_cess :cp_vat_invoice_date :cp_vat_invoice_number :cp_vat_tax :p_conc_request_id :p_rma_reference :p_transaction_id 1 10 :p_rma_reference LOV Oracle RMA_REFR Y select a.rma_reference id, a.rma_reference value, null description from rcv_transactions a,rcv_shipment_headers b,po_vendors c where rma_reference is not null and a.shipment_header_id=b.shipment_header_id and transaction_type='RETURN TO VENDOR' and a.vendor_id=c.vendor_id order by value,description Y US RMA Reference ZHS RMA 参考 2 20 :p_transaction_id LOV Oracle JAI_SRS_BATCH_NUM_JAINRECI Y select jrrbt.transaction_id id, decode(excise_batch_num,null,vat_batch_num,excise_batch_num) value, null description from jai_rcv_rtv_batch_trxs jrrbt where excise_batch_num is not null or vat_batch_num is not null order by value,description Y US Batch Number ZHS 批号