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 |