JA India - Outside Processing Challan - draft

Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: India - Outside Processing Challan (XML) - Not Supported: Reserved For Future Use
Short Name: JAIN57F4_XML
DB package: JA_JAIN57F4_XMLP_PKG
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
Parameter Name SQL text Validation
Organization Name
 
LOV Oracle
Location Name
 
LOV Oracle
Fin Year
 
LOV Oracle
From Date
 
Date
Issue Dates To
 
Date
Form Numbers From
 
LOV Oracle
Form Numbers To
 
LOV Oracle
Vendor Name
 
LOV Oracle
Place
 
Char