PO Printed Planning Schedule

Description
Categories: BI Publisher, Procurement
Application: Purchasing
Source: Printed Planning Schedule (XML)
Short Name: CHVPRSCH_XML
DB package: PO_CHVPRSCH_XMLP_PKG
select
csh.schedule_id,
csh.schedule_num,
csh.schedule_type ,
polc1.displayed_field Type,
polc2.displayed_field Status,
csh.schedule_horizon_start Horizon_Start,
csh.schedule_horizon_end Horizon_End,
cbp.bucket_pattern_name Bucket,
csh.schedule_revision Rev,
csh.mrp_compile_designator MRP_Plan,
csh.mps_schedule_designator MSP_Plan,
csh.drp_compile_designator DRP_Plan,
pov.vendor_name Supplier,
povs.vendor_site_code Site,
povs.address_line1 A1ddress,
povs.address_line2 A2ddress,
povs.address_line3 A3ddress,
povs.city||', '||
povs.state||' '||
povs.zip A4ddress
from chv_schedule_headers csh, 
chv_bucket_patterns cbp,
po_vendors pov, 
po_vendor_sites povs,
po_lookup_codes polc1,
po_lookup_codes polc2
where csh.bucket_pattern_id = cbp.bucket_pattern_id
and csh.vendor_id = pov.vendor_id
and csh.vendor_site_id = povs.vendor_site_id
and csh.schedule_subtype = polc1.lookup_code
and polc1.lookup_type = decode(csh.schedule_type, 'PLAN_SCHEDULE','PLAN_SCHEDULE_SUBTYPE',
			'SHIP_SCHEDULE_SUBTYPE')
and csh.schedule_status = polc2.lookup_code
and polc2.lookup_type = 'SCHEDULE_STATUS'
and nvl(:P_schedule_sub, polc1.displayed_field) = polc1.displayed_field
and nvl(:P_schedule_type, csh.schedule_type) = csh.schedule_type
and pov.vendor_name between nvl(:P_vendor_name_from, pov.vendor_name)
	                      and nvl(:P_vendor_name_to,pov.vendor_name)
and nvl(:P_site, povs.vendor_site_code) = povs.vendor_site_code
and nvl(:P_schedule_num, csh.schedule_num) = csh.schedule_num
and nvl(:P_schedule_rev, csh.schedule_revision) = csh.schedule_revision
and nvl(:P_horizon_start, csh.schedule_horizon_start) between
	csh.schedule_horizon_start and csh.schedule_horizon_end
and nvl(:P_horizon_end, csh.schedule_horizon_end) between
                  csh.schedule_horizon_start and csh.schedule_horizon_end
and (nvl(:P_test_print, 'N') = 'Y'
         OR
        (nvl(:P_test_print, 'N') = 'N'
          and csh.schedule_status = 'CONFIRMED'))
and nvl(:P_autoschedule_flag, 'N') = 'N'
and (exists (select 1 from chv_item_orders cio where cio.schedule_id =
                     csh.schedule_id) or
         nvl(:P_EXCLUDE_ZERO_QUANTITY_LINES, 'N') = 'N')
union
select
csh.schedule_id,
csh.schedule_num,
csh.schedule_type ,
polc1.displayed_field Type,
polc2.displayed_field Status,
csh.schedule_horizon_start Horizon_Start,
csh.schedule_horizon_end Horizon_End,
cbp.bucket_pattern_name Bucket,
csh.schedule_revision Rev,
csh.mrp_compile_designator MRP_Plan,
csh.mps_schedule_designator MSP_Plan,
csh.drp_compile_designator DRP_Plan,
pov.vendor_name Supplier,
povs.vendor_site_code Site,
povs.address_line1 A1ddress,
povs.address_line2 A2ddress,
povs.address_line3 A3ddress,
povs.city||', '||
povs.state||' '||
povs.zip A4ddress
from chv_schedule_headers csh,
chv_bucket_patterns cbp,
po_vendors pov,
po_vendor_sites povs,
po_lookup_codes polc1,
po_lookup_codes polc2
where csh.bucket_pattern_id = cbp.bucket_pattern_id
and csh.vendor_id = pov.vendor_id
and csh.vendor_site_id = povs.vendor_site_id
and csh.schedule_subtype = polc1.lookup_code
and polc1.lookup_type = decode(csh.schedule_type, 'PLAN_SCHEDULE','PLAN_SCHEDULE_SUBTYPE',
                        'SHIP_SCHEDULE_SUBTYPE')
and csh.schedule_status = polc2.lookup_code
and polc2.lookup_type = 'SCHEDULE_STATUS'
and (nvl(:P_test_print, 'N') = 'Y'
         OR
        (nvl(:P_test_print, 'N') = 'N'
          and csh.schedule_status = 'CONFIRMED'))
and :P_autoschedule_flag = 'Y'
and :P_batch_id = csh.batch_id
and (exists (
select 1
from chv_item_orders cio where cio.schedule_id =
                    csh.schedule_id) or
         nvl(:P_EXCLUDE_ZERO_QUANTITY_LINES, 'N') = 'N')
order by schedule_num
Parameter NameSQL textValidation
p_autoschedule_flag
 
LOV Oracle
P_ORGANIZATION_ID
 
Number
Schedule Type
 
p_exclude_zero_quantity_lines
 
LOV Oracle
Dynamic Precision Option
 
LOV Oracle
Test Print
 
LOV Oracle
Supplier Site
 
LOV Oracle
Supplier To
 
LOV Oracle
Supplier From
 
LOV Oracle
Horizon End
 
Date
Horizon Start
 
Date
Schedule Subtype
 
LOV Oracle
Schedule Revison
 
LOV Oracle
Schedule Number
 
LOV Oracle