execute fnd_client_info.set_org_context('204');
SELECT DECODE (por.release_num,
NULL, poh.segment1,
poh.segment1 || '-' || por.release_num)
PO_Number_Release,
pol.line_num Line,
pov.vendor_name Vendor,
pol.item_revision Rev,
pol.item_description Description,
pll.shipment_num,
pod.distribution_num Distribution,
DECODE (plt.order_type_lookup_code,
'AMOUNT', NULL,
pll.price_override)
Unit_Price,
pll.promised_date Promised_Date,
pol.unit_meas_lookup_code Unit,
DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_ordered,
'FIXED PRICE', POD.amount_ordered,
POD.quantity_ordered)
Quantity_Amount_Ordered,
DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_billed,
'FIXED PRICE', POD.amount_billed,
POD.quantity_billed)
Quantity_Amount_Billed,
DECODE (POL.order_type_lookup_code,
'RATE', POD.amount_delivered,
'FIXED PRICE', POD.amount_delivered,
POD.quantity_delivered)
Qty_Amount_Delivered,
DECODE (
POL.order_type_lookup_code,
'RATE',
(NVL (POD.amount_ordered, 0) - NVL (POD.amount_billed, 0))
/ DECODE (NVL (POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
'FIXED PRICE',
(NVL (POD.amount_ordered, 0) - NVL (POD.amount_billed, 0))
/ DECODE (NVL (POD.amount_ordered, 0), 0, 1, POD.amount_ordered),
(NVL (POD.quantity_ordered, 0) - NVL (POD.quantity_billed, 0))
/ DECODE (NVL (POD.quantity_ordered, 0),
0, 1,
POD.quantity_ordered)
)
* 100
Percent_Unbilled,
DECODE (
POL.order_type_lookup_code,
'RATE',
POD.amount_ordered
- NVL (POD.amount_cancelled, 0)
- NVL (POD.amount_billed, 0),
'FIXED PRICE',
POD.amount_ordered
- NVL (POD.amount_cancelled, 0)
- NVL (POD.amount_billed, 0),
( POD.quantity_ordered
- NVL (POD.quantity_cancelled, 0)
- NVL (POD.quantity_billed, 0))
* PLL.price_override
)
C_AMOUNT_OPEN_INV,
poh.po_header_id,
pol.po_line_id,
por.release_num,
poh.currency_code C_CURRENCY,
NVL (por.po_release_id, -1) release_id
FROM po_distributions pod,
mtl_system_items msi,
po_line_locations pll,
po_lines pol,
po_releases por,
po_headers poh,
po_vendors pov,
financials_system_parameters fsp,
po_line_types plt
WHERE poh.segment1 = '804'
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
AND pol.item_id = msi.inventory_item_id(+)
AND msi.organization_id = fsp.inventory_organization_id
AND poh.vendor_id = pov.vendor_id(+)
AND pll.po_release_id = por.po_release_id(+)
AND pol.line_type_id = plt.line_type_id
AND pll.shipment_type IN ('STANDARD', 'BLANKET', 'SCHEDULED')
AND NVL (pol.closed_code, 'OPEN') NOT IN
('CLOSED', 'FINALLY CLOSED')
AND NVL (pll.closed_code, 'OPEN') NOT IN
('CLOSED', 'FINALLY CLOSED')
AND NVL (poh.closed_code, 'OPEN') NOT IN
('CLOSED', 'FINALLY CLOSED')
AND NVL (por.closed_code, 'OPEN') NOT IN
('CLOSED', 'FINALLY CLOSED')
AND NVL (poh.cancel_flag, 'N') = 'N'
AND NVL (por.cancel_flag, 'N') = 'N'
AND NVL (pol.cancel_flag, 'N') = 'N'
AND NVL (pll.cancel_flag, 'N') = 'N'
ORDER BY pll.line_location_id
/
Query to find receipts against a PO shipment line :
/* Formatted on 1/13/2015 2:24:43 PM (QP5 v5.115.810.9015) */
SELECT pol.po_header_id,
pol.po_line_id,
pll.line_location_id,
pll.quantity,
rsh.shipment_header_id,
rsh.receipt_source_code,
rsh.vendor_id,
rsh.vendor_site_id,
rsh.organization_id,
rsh.shipment_num,
rsh.receipt_num,
rsh.ship_to_location_id,
rsh.bill_of_lading,
rsl.shipment_line_id,
rsl.QUANTITY_SHIPPED,
rsl.QUANTITY_RECEIVED,
rct.transaction_type,
rct.transaction_id,
DECODE (pol.order_type_lookup_code,
'RATE', NVL (rct.amount, 0),
'FIXED PRICE', NVL (rct.amount, 0),
NVL (rct.source_doc_quantity, 0))
transaction_qty
FROM rcv_transactions rct,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_lines pol,
po_line_locations pll
WHERE rct.po_line_location_id = 28302
AND rct.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
('RATE', 'FIXED PRICE')
AND rct.shipment_line_id = rsl.shipment_line_id
AND rsl.shipment_header_id = rsh.shipment_header_id
ORDER BY rct.transaction_id
/
Query to find PO returns :
/* Formatted on 1/13/2015 2:24:58 PM (QP5 v5.115.810.9015) */
SELECT pol.po_header_id,
pol.po_line_id,
rct.po_line_location_id Line_location_id,
SUM ( (NVL (rct.source_doc_quantity, 0))) Qty_returned
FROM rcv_transactions rct, po_lines pol, po_line_locations pll
WHERE rct.transaction_type = 'RETURN TO VENDOR'
AND rct.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
('RATE', 'FIXED PRICE')
GROUP BY pol.po_header_id, pol.po_line_id, rct.po_line_location_id
UNION ALL
SELECT pol.po_header_id,
pol.po_line_id,
rct.po_line_location_id Line_location_id,
SUM ( (NVL (rct.amount, 0))) Qty_returned
FROM rcv_transactions rct, po_lines pol, po_line_locations pll
WHERE rct.transaction_type = 'RETURN TO VENDOR'
AND rct.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN
('RATE', 'FIXED PRICE')
GROUP BY pol.po_header_id, pol.po_line_id, rct.po_line_location_id
/
Query to find PO corrections :
/* Formatted on 1/13/2015 2:26:28 PM (QP5 v5.115.810.9015) */
SELECT pol.po_header_id,
pol.po_line_id,
rct.po_line_location_id Line_location_id,
SUM (NVL (rct1.source_doc_quantity, 0)) Qty_corrected
FROM rcv_transactions rct,
rcv_transactions rct1,
po_lines pol,
po_line_locations pll
WHERE rct.transaction_type IN ('RECEIVE', 'MATCH')
AND rct.po_line_location_id = pll.line_location_id
AND rct1.transaction_type = 'CORRECT'
AND rct1.parent_transaction_id = rct.transaction_id
AND rct1.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN
('RATE', 'FIXED PRICE')
GROUP BY pol.po_header_id, pol.po_line_id, rct.po_line_location_id
UNION ALL
SELECT pol.po_header_id,
pol.po_line_id,
rct.po_line_location_id Line_location_id,
SUM (NVL (rct1.amount, 0)) Qty_corrected
FROM rcv_transactions rct,
rcv_transactions rct1,
po_lines pol,
po_line_locations pll
WHERE rct.transaction_type IN ('RECEIVE', 'MATCH')
AND rct.po_line_location_id = pll.line_location_id
AND rct1.transaction_type = 'CORRECT'
AND rct1.parent_transaction_id = rct.transaction_id
AND rct1.po_line_location_id = pll.line_location_id
AND rct.po_line_id = pol.po_line_id
AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN
('RATE', 'FIXED PRICE')
GROUP BY pol.po_header_id, pol.po_line_id, rct.po_line_location_id
No comments:
Post a Comment