[Home] [Help]
40: , poh.note_to_receiver
41: , to_char(poh.org_id) --
42: FROM rcv_supply ms
43: , rcv_transactions rt
44: , po_headers_all poh
45: , po_lines_all pol
46: , po_line_types plt
47: , mtl_parameters mp
48: WHERE poh.po_header_id = ms.po_header_id
89: to_char(poh.org_id) --
90:
91: FROM rcv_Supply ms ,
92: rcv_Transactions rt,
93: po_Headers_All poh ,
94: mtl_Parameters mp
95: WHERE poh.po_Header_Id = ms.po_Header_Id
96: AND ms.Quantity > 0
97: AND ms.Supply_Type_Code = 'RECEIVING'
155: , poh.vendor_site_id
156: , 'Vendor'
157: , poh.note_to_receiver
158: , to_char(poh.org_id) --
159: FROM po_headers poh
160: WHERE exists (SELECT 'Valid PO Shipments'
161: FROM po_line_locations_all poll
162: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
163: , mtl_parameters mp,
197: , poh.vendor_site_id
198: , 'Vendor'
199: , poh.note_to_receiver
200: , to_char(poh.org_id) --
201: FROM po_headers_all poh
202: , rcv_shipment_lines rsl
203: WHERE exists (SELECT 'Valid PO Shipments'
204: FROM po_line_locations poll
205: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
250: RCV_TRANSACTIONS RT,
251: PO_LOOKUP_CODES PLC,
252: PO_LINES_ALL pol,
253: PO_LINE_LOCATIONS_ALL PLL,
254: PO_HEADERS_ALL POH,
255: po_line_types plt,--BUG 5166887
256: mtl_parameters mp --BUG 5166887
257: -- For Bug 7440217 Added RCV_PRAMETERS to find out if the organization is LCM enabled or not
258: , rcv_parameters rp
940: WHERE msik.inventory_item_id = rsl.item_id
941: AND msik.organization_id = p_organization_id
942: AND msik.mtl_transactions_enabled_flag = 'N')
943: OR exists
944: (select '1' from po_headers_all poh
945: , po_lines_all pol
946: , po_line_types plt
947: where rt.po_header_id is not null
948: and rt.po_header_id = poh.po_header_id
1111: WHERE msik.inventory_item_id = rsl.item_id
1112: AND msik.organization_id = p_organization_id
1113: AND msik.mtl_transactions_enabled_flag = 'N')
1114: OR exists (select '1'
1115: from po_headers_all poh
1116: , po_lines_all pol
1117: , po_line_types plt
1118: where rt.po_header_id is not null
1119: and rt.po_header_id = poh.po_header_id
1459: a. In the condition, "AND NVL(poll.approved_flag,'N') = 'Y' " ,
1460: nvl() has been removed.
1461: b. Removed the tables mtl_system_items_kfv and mtl_units_of_measure which are
1462: joined with po_lines_all table through outer join.
1463: c. Added the hint " +index(POH PO_HEADERS_U2) " to use the index PO_HEADERS_U2.
1464: d. Removed the Distinct clause from the select statement.
1465: */
1466:
1467: -- Fix for the performance Bug 4638235
1492: --bug 4638235 added checking for l_doc_number passed
1493: -- This select takes care of Vendor Item and any non-expense item
1494: -- and cross ref item case.
1495: OPEN x_doc_num_lov FOR
1496: SELECT /*+index(POH PO_HEADERS_U2) */
1497: -- DISTINCT
1498: -- DOCTYPE PO
1499: meaning FIELD0 ,
1500: poh.segment1 FIELD1 ,
1510: NULL FIELD11 ,
1511: NULL FIELD12 ,
1512: lookup_code FIELD13 ,
1513: to_char(poh.org_id) FIELD14 --
1514: FROM po_headers poh,
1515: fnd_lookup_values_vl flv
1516: WHERE flv.lookup_code = 'PO'
1517: AND flv.lookup_type = 'DOC_TYPE'
1518: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
1555: -- End for Bug 7440217
1556: )
1557: UNION
1558: -- This Select Handles Substitute Items
1559: SELECT /*+index(POH PO_HEADERS_U2) */
1560: -- DISTINCT
1561: -- DOCTYPE PO
1562: meaning FIELD0 ,
1563: poh.segment1 FIELD1 ,
1573: NULL FIELD11 ,
1574: NULL FIELD12 ,
1575: lookup_code FIELD13 ,
1576: to_char(poh.org_id) FIELD14 --
1577: FROM po_headers poh,
1578: fnd_lookup_values_vl flv
1579: WHERE flv.lookup_code = 'PO'
1580: AND flv.lookup_type = 'DOC_TYPE'
1581: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
1662: NULL FIELD11,
1663: NULL FIELD12,
1664: lookup_code FIELD13,
1665: to_char(poh.org_id) FIELD14 --
1666: FROM po_headers poh,
1667: fnd_lookup_values_vl flv ,
1668: po_lines_all pl,
1669: po_line_locations_all poll
1670: -- For Bug 7440217 Added MTL_PARAMETERS and RCV_PRAMETERS to find out if the organization is LCM enabled or not
1720: NULL FIELD11 ,
1721: NULL FIELD12 ,
1722: lookup_code FIELD13,
1723: to_char(poh.org_id) FIELD14 --
1724: FROM po_headers poh,
1725: fnd_lookup_values_vl flv ,
1726: po_lines_all pl ,
1727: mtl_related_items mri ,
1728: mtl_system_items_kfv msi ,
1782: NULL FIELD11 ,
1783: NULL FIELD12 ,
1784: lookup_code FIELD13,
1785: to_char(poh.org_id) FIELD14 --
1786: FROM po_headers poh,
1787: fnd_lookup_values_vl flv ,
1788: po_lines_all pl ,
1789: mtl_related_items mri ,
1790: mtl_system_items_kfv msi ,
1831:
1832: ELSIF p_item_description IS NOT NULL THEN
1833: OPEN x_doc_num_lov FOR
1834: -- This Select Handles Expense Items
1835: SELECT /*+index(POH PO_HEADERS_U2) */
1836: -- DISTINCT
1837: -- DOCTYPE PO
1838: meaning FIELD0 ,
1839: poh.segment1 FIELD1 ,
1849: NULL FIELD11 ,
1850: NULL FIELD12 ,
1851: lookup_code FIELD13 ,
1852: to_char(poh.org_id) FIELD14 --
1853: FROM po_headers poh,
1854: fnd_lookup_values_vl flv
1855: WHERE flv.lookup_code = 'PO'
1856: AND flv.lookup_type = 'DOC_TYPE'
1857: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
1898: ELSE
1899: --Both Inventory Item Id and Item Desc are Null
1900: --And also Both Item Id and Item Desc won't be passed together.
1901: OPEN x_doc_num_lov FOR
1902: SELECT /*+index(POH PO_HEADERS_U2) */
1903: -- DISTINCT
1904: -- DOCTYPE PO
1905: meaning FIELD0 ,
1906: poh.segment1 FIELD1 ,
1916: NULL FIELD11 ,
1917: NULL FIELD12 ,
1918: lookup_code FIELD13 ,
1919: to_char(poh.org_id) FIELD14 --
1920: FROM po_headers poh,
1921: fnd_lookup_values_vl flv
1922: WHERE flv.lookup_code = 'PO'
1923: AND flv.lookup_type = 'DOC_TYPE'
1924: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
2376: ELSIF NVL(p_doc_type,'ALL') = 'ALL' THEN
2377: OPEN x_doc_num_lov FOR
2378: -- This select takes care of Vendor Item and any non-expense item
2379: -- and cross ref item case.
2380: SELECT /*+index(POH PO_HEADERS_U2) */
2381: DISTINCT
2382: -- DOCTYPE PO
2383: meaning FIELD0 ,
2384: poh.segment1 FIELD1 ,
2394: NULL FIELD11 ,
2395: NULL FIELD12 ,
2396: lookup_code FIELD13 ,
2397: to_char(poh.org_id) FIELD14 --
2398: FROM po_headers poh,
2399: fnd_lookup_values_vl flv
2400: WHERE flv.lookup_code = 'PO'
2401: AND flv.lookup_type = 'DOC_TYPE'
2402: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
2440: )
2441: AND p_item_description IS NULL
2442: UNION
2443: -- This Select Handles Substitute Items
2444: SELECT /*+index(POH PO_HEADERS_U2) */
2445: DISTINCT
2446: -- DOCTYPE PO
2447: meaning FIELD0 ,
2448: poh.segment1 FIELD1 ,
2458: NULL FIELD11 ,
2459: NULL FIELD12 ,
2460: lookup_code FIELD13 ,
2461: to_char(poh.org_id) FIELD14 --
2462: FROM po_headers poh,
2463: fnd_lookup_values_vl flv
2464: WHERE flv.lookup_code = 'PO'
2465: AND flv.lookup_type = 'DOC_TYPE'
2466: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
2521: )
2522: AND p_item_description IS NULL
2523: UNION
2524: -- This Select Handles Expense Items
2525: SELECT /*+index(POH PO_HEADERS_U2) */
2526: DISTINCT
2527: -- DOCTYPE PO
2528: meaning FIELD0 ,
2529: poh.segment1 FIELD1 ,
2539: NULL FIELD11 ,
2540: NULL FIELD12 ,
2541: lookup_code FIELD13 ,
2542: to_char(poh.org_id) FIELD14 --
2543: FROM po_headers poh,
2544: fnd_lookup_values_vl flv
2545: WHERE flv.lookup_code = 'PO'
2546: AND flv.lookup_type = 'DOC_TYPE'
2547: AND nvl(flv.start_date_active, SYSDATE) <= SYSDATE
3037: , woov.department_code
3038: , pda.po_distribution_id
3039: , (pda.quantity_ordered - pda.quantity_delivered)
3040: , woov.repetitive_schedule_id
3041: FROM po_headers_all phl
3042: , po_lines_all pla
3043: , po_distributions_all pda
3044: , po_releases_all prl
3045: , wip_osp_jobs_val_v wojv
3075: , woov.department_code
3076: , pda.po_distribution_id
3077: , (pda.quantity_ordered - pda.quantity_delivered)
3078: , woov.repetitive_schedule_id
3079: FROM po_headers_all phl
3080: , po_lines_all pla
3081: , po_distributions_all pda
3082: , wip_osp_jobs_val_v wojv
3083: , wip_osp_lines_val_v wolv