JA India - Outside Processing Challan - draft

SELECT
j57f4.form_id,
 j57f4.form_number CHALLAN_NO,
 hou.name NAME,
 loc.address_line_1||','||loc.address_line_2||','||loc.address_line_3||','||loc.town_or_city||','||   loc.country ADDRS,
 pov.vendor_name||','|| povs.address_line1||','||  povs.address_line2||','|| povs.address_line3||','||   povs.city||','|| povs.country VENDOR,
 msi.description DESCRIPTION,
 j57f4l.identification IDENTIFICATION,
 j57f4l.tariff_code TCODE,
 j57f4l.despatch_qty QUANTITY,
 j57f4l.item_uom IUOM,
 j57f4l.item_value ASSES_VALUE,
 j57f4l.excise_rate ED_RATE,
 ((j57f4l.despatch_qty * j57f4l.item_value *  j57f4l.excise_rate) / 100) DUTY_AMT,
 j57f4l.process_reqd PROCESS_REQD,
 j57f4.process_time PROCESS_TIME,
 j57f4.dr_register REGISTER_NAME,
 j57f4.dr_reg_slno DR_SLNO,
 j57f4.dr_reg_entry_date DR_DATE,
 j57f4.creation_date FRM_DATE ,
 to_char(j57f4.issue_date, 'DD-MON-YYYY HH:MI:SS') ISSUE_DATE,
j57f4.print_original ,
j57f4.print_duplicate,
j57f4.vendor_id ,
j57f4.vendor_site_id ,
MSI.concatenated_segments ITEM_CODE,
j57f4.po_header_id,
j57f4.oth_doc_id PO_RELEASE_ID,
j57f4.organization_id,
j57f4l.wip_entity_id,
j57f4l.assembly_id,
j57f4.cancel_flag,
	JA_JAIN57F4_XMLP_PKG.cf_1formula(j57f4.cancel_flag, j57f4.print_original) CF_1_PRINT,
	JA_JAIN57F4_XMLP_PKG.cf_vendor_site_nameformula(j57f4.vendor_id, j57f4.vendor_site_id) CF_VENDOR_SITE_NAME,
	JA_JAIN57F4_XMLP_PKG.cf_po_numberformula(j57f4.po_header_id) CF_PO_NUMBER,
	JA_JAIN57F4_XMLP_PKG.cf_release_numberformula(j57f4.oth_doc_id) CF_RELEASE_NUMBER,
	JA_JAIN57F4_XMLP_PKG.cf_job_nameformula(j57f4l.wip_entity_id, j57f4.organization_id) CF_JOB_NAME,
	JA_JAIN57F4_XMLP_PKG.cf_job_start_dateformula(j57f4l.wip_entity_id, j57f4.organization_id) CF_JOB_START_DATE,
	JA_JAIN57F4_XMLP_PKG.cf_assembly_item_codeformula(j57f4l.assembly_id, j57f4.organization_id) CF_ASSEMBLY_ITEM_CODE
FROM
         JAI_PO_OSP_HDRS j57f4,
         JAI_PO_OSP_LINES j57f4L,
         hr_organization_units HOU,
         hr_locations LOC,
         po_vendors POV,
         po_vendor_sites_all POVS,
         mtl_system_items_kfv MSI
   WHERE
 trunc(j57f4.issue_date)           <= nvl(:P_TO_DATE,trunc(sysdate))
 AND j57f4.organization_id = nvl(:p_organization_id,j57f4.organization_id)
 AND j57f4.location_id = nvl(:p_location_id,j57f4.location_id)
 AND j57f4.fin_year = nvl(:p_fin_year,j57f4.fin_year)
 AND trunc(j57f4.issue_date)       >= trunc(nvl(:P_FROM_DATE,trunc(j57f4.issue_date)))
 AND trunc(j57f4.form_id)  >= nvl(:P_FORM_ID_FROM,trunc(j57f4.form_id))
 AND trunc(j57f4.form_id)  <= nvl(:P_FORM_ID_TO,trunc(j57f4.form_id))
 AND upper(j57f4.issue_approved) = 'Y'
 AND j57f4.vendor_id             = nvl(:P_VENDOR_ID,j57f4.vendor_id)
 AND j57f4l.form_id              = j57f4.form_id
 AND hou.organization_id         = j57f4.organization_id
 AND loc.location_id             = j57f4.location_id
 AND pov.vendor_id               = j57f4.vendor_id
 AND povs.vendor_site_id (+)        = j57f4.vendor_site_id
 AND msi.organization_id         = j57f4.organization_id
 AND msi.inventory_item_id       = j57f4l.item_id
and ( j57f4.print_original = 'Y' or j57f4.print_duplicate = 'Y' or nvl(j57f4.cancel_flag,'N') = 'Y')
ORDER BY
msi.description