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
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
Parameter NameSQL textValidation
Batch Number
 
LOV Oracle
RMA Reference
 
LOV Oracle