/*Purchase Order Master Query In Oracle Apps R12*/
SELECT xx.org_id,
(SELECT organization_name
FROM apps.org_organization_definitions
WHERE organization_id = xx.org_id) ou_name,
xx.po_type,
xx.po_num,
Trunc (xx.po_date) po_date,
(SELECT Listagg(release_num, ', ')
within GROUP (ORDER BY release_num)
FROM po_releases_all
WHERE po_release_id = xx.released_id) release_num,
(SELECT Listagg(To_char (release_date), ', ')
within GROUP (ORDER BY To_char (release_date))
FROM po_releases_all
WHERE po_release_id = xx.released_id) release_date,
xx.shipment_num,
xx.line_num,
xx.item,
( CASE
WHEN xx.line_type_id = 3
AND xx.osp_description IS NOT NULL THEN xx.item_description
|| ' FOR-> '
|| xx.osp_description
ELSE xx.item_description
END ) item_description,
xx.need_by,
xx.unit_price,
xx.pending,
xx.pend_qty_val,
Nvl (xx.quantity, 0) item_qty,
Nvl (xx.quantity, 0) * xx.unit_price VALUE,
xx.currency_code,
xx.rate,
xx.vendor_code,
name,
xx.wip_entity_name,
xx.status_code
FROM (SELECT abc.org_id,
abc.po_type,
abc.currency_code,
abc.po_release_id released_id,
abc.po_line_id,
CASE
WHEN abc.wip_entity_id IS NULL THEN
Item_code (abc.inventory_item_id)
WHEN abc.wip_entity_id IS NOT NULL THEN abc.segment1
END item,
CASE
WHEN abc.wip_entity_id IS NULL THEN abc.inventory_item_id
WHEN abc.wip_entity_id IS NOT NULL THEN abc.inventory_item_id
END item_id,
CASE
WHEN abc.wip_entity_id IS NULL THEN
Xxfilix_item_desc_po(abc.po_line_id)
WHEN abc.wip_entity_id IS NOT NULL THEN abc.des
END item_description,
(SELECT description
FROM mtl_system_items_b
WHERE inventory_item_id = abc.primary_item_id
AND organization_id = 112) osp_description,
abc.remark,
abc.po_num,
abc.po_date,
abc.quantity,
abc.quantity_received,
abc.quantity_cancelled,
abc.pending,
abc.pend_qty_val,
abc.unit_price,
abc.vendor_code,
abc.name,
abc.need_by,
abc.source,
abc.wip_entity_name,
abc.status_code,
abc.primary_item_id,
abc.shipment_num,
abc.line_num,
abc.rate,
abc.line_type_id
FROM (SELECT
xyz.po_type,
xyz.currency_code,
Decode(XYZ.destination_context, 'SHOP FLOOR', xyz.primary_item_id)primary_item_id,
xyz.wip_entity_id,
xyz.segment1,
xyz.org_id,
xyz.inventory_item_id,
xyz.item_revision,
xyz.vendor_id,
xyz.des,
xyz.remark,
xyz.po_num,
xyz.po_date,
xyz.quantity,
xyz.quantity_received,
xyz.quantity_cancelled,
xyz.pending,
xyz.pend_qty_val,
xyz.unit_price,
xyz.vendor_code,
xyz.name,
xyz.need_by,
xyz.po_release_id,
xyz.po_line_id,
xyz.po_header_id,
xyz.source,
xyz.wip_entity_name,
xyz.status_code,
xyz.shipment_num,
xyz.line_num,
xyz.rate,
xyz.line_type_id
FROM (SELECT DISTINCT abc1.po_type,
abc1.currency_code,
abc1.po_release_id,
abc1.po_line_id,
abc1.po_header_id,
abc1.line_location_id,
abc1.vendor_id,
abc1.segment1,
abc1.org_id,
abc1.inventory_item_id,
abc1.item_revision,
abc1.des,
abc1.remark,
abc1.po_num,
abc1.po_date,
abc1.quantity,
abc1.quantity_received,
abc1.quantity_cancelled,
abc1.pending,
abc1.pend_qty_val,
abc1.unit_price,
abc1.vendor_code,
abc1.name,
abc1.need_by,
jih.source,
pda.wip_entity_id,
we.wip_entity_name,
abc1.closed_code status_code,
abc1.shipment_num,
abc1.line_num,
abc1.rate,
abc1.line_type_id,
pda.destination_context,
we.primary_item_id
FROM (SELECT pha.type_lookup_code
po_type,
pha.currency_code,
pllv.po_release_id,
pllv.po_line_id,
pllv.po_header_id,
pllv.line_location_id,
pv.vendor_id,
msi.segment1,
msi.organization_id
org_id,
msi.inventory_item_id,
pla.item_revision,
pla.item_description
des
,
pllv.shipment_num,
pla.line_num,
pla.line_type_id,
Decode(pla.attribute1, '0', NULL,
pla.attribute1)
remark,
pha.segment1
po_num,
pha.creation_date
po_date,
pllv.quantity,
pllv.quantity_received,
pllv.quantity_cancelled,
( pllv.quantity - pllv.quantity_received -
pllv.quantity_cancelled )
pending,
Round(( ( pllv.quantity - pllv.quantity_received -
pllv.quantity_cancelled ) *
( pllv.price_override ) ), 2)
pend_qty_val,
pllv.price_override
unit_price,
pv.segment1
vendor_code,
pv.vendor_name
NAME
,
Nvl(pllv.promised_date, pllv.need_by_date)
need_by,
pha.rate,
pllv.closed_code
FROM mtl_system_items msi,
po_lines_all pla,
po_line_locations_all pllv,
po_headers_all pha,
po_vendors pv
WHERE msi.inventory_item_id = pla.item_id
AND pla.po_line_id = pllv.po_line_id
AND pllv.po_header_id = pha.po_header_id
AND pha.vendor_id = pv.vendor_id
AND pllv.po_release_id IS NOT NULL
--------------AND (pllv.quantity- pllv.quantity_received) > 0
AND pllv.ship_to_organization_id = msi.organization_id
AND ( pllv.cancel_flag <> 'Y'
OR pllv.cancel_flag IS NULL )
--AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
--AND NVL (pha.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
--AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
--AND NVL (pla.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
UNION ALL
SELECT pha.type_lookup_code
po_type,
pha.currency_code,
pllv.po_release_id,
pllv.po_line_id,
pllv.po_header_id,
pllv.line_location_id,
pv.vendor_id,
msi.segment1,
msi.organization_id
org_id,
msi.inventory_item_id,
pla.item_revision,
pla.item_description
des
,
pllv.shipment_num,
pla.line_num,
pla.line_type_id,
Decode(pla.attribute1, '0', NULL,
pla.attribute1)
remark,
pha.segment1
po_num,
pha.creation_date
po_date,
pllv.quantity,
pllv.quantity_received,
pllv.quantity_cancelled,
( pllv.quantity - pllv.quantity_received -
pllv.quantity_cancelled )
pending,
Round(( ( pllv.quantity - pllv.quantity_received -
pllv.quantity_cancelled ) *
( pllv.price_override ) ), 2)
pend_qty_val,
pllv.price_override
unit_price,
pv.segment1
vendor_code,
pv.vendor_name
NAME
,
Nvl(pllv.promised_date, pllv.need_by_date)
need_by,
pha.rate,
pllv.closed_code
FROM mtl_system_items msi,
po_lines_all pla,
po_line_locations_all pllv,
po_headers_all pha,
po_vendors pv
WHERE msi.inventory_item_id = pla.item_id
AND pla.po_line_id = pllv.po_line_id
AND pllv.po_header_id = pha.po_header_id
AND pha.vendor_id = pv.vendor_id
AND pllv.po_release_id IS NULL
-------------AND (pllv.quantity- pllv.quantity_received) > 0
AND pllv.ship_to_organization_id = msi.organization_id
AND ( pllv.cancel_flag <> 'Y'
OR pllv.cancel_flag IS NULL )
--AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
--AND NVL (pha.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
--AND NVL (pllv.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
--AND NVL (pla.closed_code, 'X') NOT IN('CLOSED','FINALLY CLOSED')
AND shipment_type NOT IN( 'PLANNED', 'BLANKET' )) abc1,
po_distributions_all pda,
jai_po_osp_lines jil,
jai_po_osp_hdrs jih,
wip_entities we
WHERE 1 = 1
AND pda.po_header_id = abc1.po_header_id
AND pda.po_line_id = abc1.po_line_id
AND pda.line_location_id = abc1.line_location_id
AND jil.po_distribution_id(+) = pda.po_distribution_id
AND jih.po_header_id(+) = abc1.po_header_id
AND we.wip_entity_id(+) = pda.wip_entity_id
AND jih.oth_doc_id(+) = abc1.po_release_id
AND Nvl (jih.cancel_flag, 0) <> 'Y') xyz) abc) xx
WHERE 1 = 1
AND Nvl (xx.source, 'XXXX') NOT IN ( 'RETURN TO VENDOR' )
AND XX.po_num = '23677501237'
AND xx.released_id = (SELECT pra.po_release_id
FROM po_releases_all pra
WHERE pra.po_release_id = xx.released_id
AND pra.release_num = 5)
ORDER BY xx.name;
No comments:
Post a Comment