Query For Vendor Payment Forecast
SELECT *
FROM (SELECT pov.vendor_type_lookup_code vendor_type_code
,vtype.meaning vendor_type
,pov.vendor_name vendor_name
,msi.segment1 item_number
,poh.segment1 po_number
, poh.segment1
|| DECODE (por.release_num
,NULL, NULL
, '-' || por.release_num) || ',' || pol.line_num
|| DECODE (pll.shipment_num
,NULL, NULL
, '-' || pll.shipment_num) number_release_line
,pll.quantity line_qty
,NVL (pll.price_override, pol.unit_price) unit_price
, pll.quantity * NVL (pll.price_override, pol.unit_price)
line_amt
-- ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YY')
---- eta
,DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YY')) eta
,TO_CHAR (DECODE (pov.vendor_type_lookup_code
,'DOMESTIC VENDORS', TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY')- 7
,'INVENTORY', TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 21
,TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 7)
,'DD-MON-YY') po_ship_date
,DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') in_transit
,NVL (poh.fob_lookup_code, povs.fob_lookup_code) fob_code
,terms.NAME payment_terms
,DECODE
(terms.term_id
,NULL, TO_DATE(TO_CHAR (DECODE (pov.vendor_type_lookup_code
,'DOMESTIC VENDORS', TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY')- 7
,'INVENTORY', TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 21
,TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 7)
,'DD-MON-YYYY') ,'DD-MON-YYYY')
,ap_create_pay_scheds_pkg.calc_due_date
(TO_DATE(TO_CHAR (DECODE (pov.vendor_type_lookup_code
,'DOMESTIC VENDORS', TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY')- 7
,'INVENTORY', TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 21
,TO_DATE(DECODE(DECODE
((SELECT 1
FROM rcv_shipment_lines rsl
,rcv_shipment_headers rsh
WHERE 1 = 1
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND rsl.source_document_code = 'PO'
AND rsl.po_line_location_id = pll.line_location_id
AND rsh.asn_type IN ('ASN', 'ASBN')
AND rsl.shipment_line_status_code IN
('EXPECTED'
,'PARTIALLY RECEIVED'
,'FULLY RECEIVED')
AND rsh.shipment_header_id = rsl.shipment_header_id
AND (NOT EXISTS (
SELECT ' '
FROM rcv_transactions_interface rti
WHERE rti.shipment_header_id =
rsl.shipment_header_id
AND rti.shipment_line_id =
rsl.shipment_line_id))
AND NVL (poh.org_id, -99) = NVL (pll.org_id, -99)
AND ROWNUM <= 1)
,NULL, 'N'
,'Y') ,'Y',
(select max(to_char(rch.expected_receipt_date,'DD-MON-YYYY')) from rcv_shipment_lines rcl,
rcv_shipment_headers rch
WHERE rcl.po_line_id = pol.po_line_id
AND rcl.po_header_id = pol.po_header_id
AND rcl.po_line_location_id = pll.line_location_id
and rcl.item_id = pol.item_id
and rcl.shipment_header_id = rch.shipment_header_id
) ,TO_CHAR (NVL (pll.promised_date, need_by_date), 'DD-MON-YYYY')) ,'DD-MON-YYYY') - 7)
,'DD-MON-YYYY') ,'DD-MON-YYYY')
,terms.term_id
,NULL
,1
,NULL)) due_date
,pll.ship_to_location_id location_id
,pol.unit_meas_lookup_code unit
,poh.po_header_id head_id
,pll.line_location_id loc_id
,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5||'.'||gcc.segment6 charge_account
FROM po_vendors pov
,po_vendor_sites_all povs
,po_releases_all por
,po_line_types plt
,mtl_categories mca
,po_line_locations_all pll
,po_lines_all pol
,po_headers_all poh
,mtl_system_items_kfv msi
,po_distributions_all pod
,fnd_lookup_values_vl vtype
,ap_terms terms
,hr_operating_units hou
,gl_code_combinations gcc
WHERE poh.po_header_id = pol.po_header_id
AND poh.terms_id = terms.term_id(+)
AND povs.vendor_id = poh.vendor_id
AND povs.vendor_site_id = poh.vendor_site_id
AND vtype.lookup_type = 'VENDOR TYPE'
AND vtype.lookup_code = pov.vendor_type_lookup_code
AND ((:P_CLOSED_CODE = 'OPEN' AND DECODE (poh.type_lookup_code
,'STANDARD', NVL (poh.closed_code, 'OPEN')
,'PLANNED', NVL (poh.closed_code, 'OPEN')
,'BLANKET', NVL (por.closed_code, 'OPEN')) = 'OPEN'
)
OR
/*(:P_CLOSED_CODE = 'CLOSED' AND DECODE (poh.type_lookup_code
,'STANDARD', NVL (poh.closed_code, 'OPEN')
,'PLANNED', NVL (poh.closed_code, 'OPEN')
,'BLANKET', NVL (por.closed_code, 'OPEN')) IN ('CLOSED', 'FINALLY CLOSED')
) */
(:P_CLOSED_CODE = 'CLOSED' AND 1=1
)
)
AND ((:P_CLOSED_CODE = 'OPEN' AND DECODE (poh.type_lookup_code
,'STANDARD', NVL (pol.closed_code, 'OPEN')
,'PLANNED', NVL (pol.closed_code, 'OPEN')
,'BLANKET', 'OPEN') = 'OPEN'
)
OR (:P_CLOSED_CODE = 'CLOSED' AND DECODE (poh.type_lookup_code
,'STANDARD', NVL (pol.closed_code, 'OPEN')
,'PLANNED', NVL (pol.closed_code, 'OPEN')
,'BLANKET', 'OPEN') IN ('CLOSED', 'FINALLY CLOSED')
)
)
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (pol.cancel_flag, 'N') = 'N'
AND NVL (por.cancel_flag, 'N') = 'N'
AND NVL (pll.cancel_flag, 'N') = 'N'
AND poh.vendor_id = pov.vendor_id
AND pol.po_line_id = pll.po_line_id
AND pol.line_type_id = plt.line_type_id
AND pol.item_id = msi.inventory_item_id(+)
AND pll.ship_to_organization_id = msi.organization_id
AND pol.category_id = mca.category_id
AND pll.po_release_id = por.po_release_id(+)
AND pll.shipment_type IN ('STANDARD', 'SCHEDULED', 'BLANKET')
AND NVL (pll.approved_flag, 'N') = 'Y'
AND NVL (pll.cancel_flag, 'N') = 'N'
AND poh.type_lookup_code IN ('BLANKET', 'STANDARD', 'PLANNED')
AND ((:P_CLOSED_CODE = 'OPEN' AND NVL (pll.closed_code, 'OPEN') IN ('OPEN','CLOSED FOR INVOICE')
AND NVL (pll.quantity, 0) - NVL (pll.quantity_cancelled, 0) >
NVL (pll.quantity_received, 0)
)
OR :P_CLOSED_CODE = 'CLOSED' AND 1=1 )
AND pll.line_location_id = pod.line_location_id(+)
AND hou.organization_id = poh.org_id
AND pod.code_combination_id = gcc.code_combination_id
-- AND poh.segment1='12345'
)
WHERE 1 = 1
AND NVL (due_date, SYSDATE) BETWEEN NVL (TO_DATE (:p_date_from
,'yyyy/mm/dd hh24:mi:ss')
, NVL (due_date, SYSDATE) - 1)
AND NVL (TO_DATE (:p_date_to
,'yyyy/mm/dd hh24:mi:ss')
, NVL (due_date, SYSDATE) + 1)
ORDER BY number_release_line;
No comments:
Post a Comment