[Home] [Help]
1262: )
1263: , X_precision) ,
1264: X_base_precision)),0)
1265: INTO X_result_fld
1266: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1267: WHERE PLL.po_header_id = X_object_id
1268: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1269: AND PLL.line_location_id = POD.line_location_id;
1270:
1290: * POD.rate / X_min_unit
1291: )
1292: * X_min_unit , X_base_precision)),0)
1293: INTO X_result_fld
1294: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1295: WHERE PLL.po_header_id = X_object_id
1296: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1297: AND PLL.line_location_id = POD.line_location_id;
1298:
1320: )
1321: * POD.rate , X_precision)
1322: / X_base_min_unit ) * X_base_min_unit) ,0)
1323: INTO X_result_fld
1324: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1325: WHERE PLL.po_header_id = X_object_id
1326: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1327: AND PLL.line_location_id = POD.line_location_id;
1328:
1347: * POD.rate /
1348: X_min_unit) * X_min_unit / X_base_min_unit)
1349: * X_base_min_unit) , 0)
1350: INTO X_result_fld
1351: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1352: WHERE PLL.po_header_id = X_object_id
1353: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1354: AND PLL.line_location_id = POD.line_location_id;
1355:
1401: * nvl(pll.price_override,0)
1402: )
1403: ,x_precision))
1404: INTO x_result_fld
1405: FROM PO_LINE_LOCATIONS_ALL PLL
1406: WHERE PLL.po_header_id = x_object_id
1407: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
1408:
1409: else
1427: )
1428: / x_min_unit)
1429: * x_min_unit)
1430: INTO x_result_fld
1431: FROM PO_LINE_LOCATIONS_ALL PLL
1432: WHERE PLL.po_header_id = x_object_id
1433: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
1434:
1435: end if;
1514: nvl(POD.quantity_cancelled, 0)) *
1515: nvl(PLL.price_override, 0) * POD.rate, X_precision) ,
1516: X_base_precision)),0)
1517: INTO X_result_fld
1518: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1519: WHERE PLL.po_header_id = X_object_id
1520: -- Bugs 482497 and 602664, lpo, 12/22/97
1521: AND PLL.shipment_type = 'SCHEDULED'
1522: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1529: nvl(POD.quantity_cancelled, 0)) *
1530: nvl(PLL.price_override, 0) * POD.rate /
1531: X_min_unit) * X_min_unit , X_base_precision)),0)
1532: INTO X_result_fld
1533: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1534: WHERE PLL.po_header_id = X_object_id
1535: -- Bugs 482497 and 602664, lpo, 12/22/97
1536: AND PLL.shipment_type = 'SCHEDULED'
1537: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1547: nvl(POD.quantity_cancelled, 0)) *
1548: nvl(PLL.price_override, 0) * POD.rate , X_precision)
1549: / X_base_min_unit ) * X_base_min_unit) ,0)
1550: INTO X_result_fld
1551: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1552: WHERE PLL.po_header_id = X_object_id
1553: -- Bugs 482497 and 602664, lpo, 12/22/97
1554: AND PLL.shipment_type = 'SCHEDULED'
1555: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1563: nvl(PLL.price_override, 0) * POD.rate /
1564: X_min_unit) * X_min_unit / X_base_min_unit)
1565: * X_base_min_unit) , 0)
1566: INTO X_result_fld
1567: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1568: WHERE PLL.po_header_id = X_object_id
1569: -- Bugs 482497 and 602664, lpo, 12/22/97
1570: AND PLL.shipment_type = 'SCHEDULED'
1571: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1603: --< Bug 3549096 > Use _ALL tables instead of org-striped views.
1604: select sum(round((pll.quantity - nvl(pll.quantity_cancelled,0))*
1605: nvl(pll.price_override,0),x_precision))
1606: INTO x_result_fld
1607: FROM PO_LINE_LOCATIONS_ALL PLL
1608: WHERE PLL.po_header_id = x_object_id
1609: -- Bugs 482497 and 602664, lpo, 12/22/97
1610: AND PLL.shipment_type = 'SCHEDULED';
1611: -- Bugs 482497 and 602664, lpo, 12/22/97
1620: nvl(pll.quantity_cancelled,0)) *
1621: nvl(pll.price_override,0)/x_min_unit)*
1622: x_min_unit)
1623: INTO x_result_fld
1624: FROM PO_LINE_LOCATIONS_ALL PLL
1625: WHERE PLL.po_header_id = x_object_id
1626: -- Bugs 482497 and 602664, lpo, 12/22/97
1627: AND PLL.shipment_type = 'SCHEDULED';
1628: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1853: functional currency and then changing this to the Contract currency */
1854:
1855: /* 716188 - Added an outer join on PO_DISTRIBUTIONS */
1856: /* 866358 - BPESCHAN: Changed the sql stmt to select quantity_ordered and
1857: quantity_cancelled from PO_DISTRIBUTIONS instead of PO_LINE_LOCATIONS.
1858: This fix prevents incorrect calculation for amount release when more then
1859: one distribution exists. */
1860:
1861: /* 958792 kbenjami 8/25/99. Proprogated fix from R11.
1873: */
1874:
1875: /*Bug 12723347
1876: 1)For execution documents with same currency as the Contract, get
1877: quantity/amount from po_line_locations_all;
1878: 2)For execution documents with different currency as the Contract, get
1879: quantity/amount from po_distributions_all;
1880:
1881: Assume most execution documents have same currency as the contract, so the
1906: nvl ( PLL.quantity_cancelled,0 ) )
1907: * nvl(pll.price_override,0) ) * nvl(POD.rate,nvl(PH1.rate,1)) / nvl (PH.rate,1),x_precision)
1908: ) ) , 0 )
1909: INTO x_result_fld
1910: FROM PO_LINE_LOCATIONS_ALL PLL,
1911: PO_DISTRIBUTIONS_ALL POD,
1912: PO_LINES_ALL PL,
1913: PO_HEADERS_ALL PH,
1914: PO_HEADERS_ALL PH1
1924: ( nvl ( PLL.amount,0) -nvl(PLL.amount_cancelled, 0) ),
1925: ( nvl ( PLL.quantity,0) - nvl ( PLL.quantity_cancelled,0 ) )
1926: * nvl(pll.price_override,0) ) , x_precision)), 0)
1927: INTO l_rel_amt_same_curr
1928: FROM PO_LINE_LOCATIONS_ALL PLL,
1929: PO_LINES_ALL PL,
1930: PO_HEADERS_ALL PH,
1931: PO_HEADERS_ALL PH1
1932: WHERE PH.po_header_id = x_object_id
1943: * nvl(pll.price_override,0) * nvl(POD.rate, nvl(PH1.rate,1))
1944: /nvl(PH.rate,1)), x_precision)
1945: ),0)
1946: INTO l_rel_amt_diff_curr
1947: FROM PO_LINE_LOCATIONS_ALL PLL,
1948: PO_DISTRIBUTIONS_ALL POD,
1949: PO_LINES_ALL PL,
1950: PO_HEADERS_ALL PH,
1951: PO_HEADERS_ALL PH1
1992: * nvl(POD.rate, nvl(PH1.rate,1))/nvl(PH.rate,1)/x_min_unit))
1993: * x_min_unit),0)
1994: INTO x_result_fld
1995: FROM PO_DISTRIBUTIONS_ALL POD,
1996: PO_LINE_LOCATIONS_ALL PLL,
1997: PO_LINES_ALL PL,
1998: PO_HEADERS_ALL PH,
1999: PO_HEADERS_ALL PH1
2000: --,FND_CURRENCIES C
2011: ( nvl ( PLL.amount,0) -nvl(PLL.amount_cancelled, 0) ),
2012: ( nvl ( PLL.quantity,0) - nvl ( PLL.quantity_cancelled,0 ) )
2013: * nvl(pll.price_override,0) )/x_min_unit) * x_min_unit), 0)
2014: INTO l_rel_amt_same_curr
2015: FROM PO_LINE_LOCATIONS_ALL PLL,
2016: PO_LINES_ALL PL,
2017: PO_HEADERS_ALL PH,
2018: PO_HEADERS_ALL PH1
2019: WHERE PH.po_header_id = x_object_id
2030: * nvl(pll.price_override,0) * nvl(POD.rate, nvl(PH1.rate,1))
2031: /nvl(PH.rate,1))/x_min_unit)
2032: * x_min_unit),0)
2033: INTO l_rel_amt_diff_curr
2034: FROM PO_LINE_LOCATIONS_ALL PLL,
2035: PO_DISTRIBUTIONS_ALL POD,
2036: PO_LINES_ALL PL,
2037: PO_HEADERS_ALL PH,
2038: PO_HEADERS_ALL PH1
2104: )
2105: * POD.rate
2106: ,x_precision),x_base_precision)),0)
2107: INTO x_result_fld
2108: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
2109: WHERE PLL.po_release_id = x_object_id
2110: AND PLL.line_location_id = POD.line_location_id
2111: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2112:
2126: )
2127: * POD.rate /
2128: X_min_unit) * X_min_unit , X_base_precision)),0)
2129: INTO x_result_fld
2130: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
2131: WHERE PLL.po_release_id = x_object_id
2132: AND PLL.line_location_id = POD.line_location_id
2133: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2134:
2151: * POD.rate
2152: , X_precision)
2153: / X_base_min_unit ) * X_base_min_unit) ,0)
2154: INTO X_result_fld
2155: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
2156: WHERE PLL.po_release_id = x_object_id
2157: AND PLL.line_location_id = POD.line_location_id
2158: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2159:
2174: * POD.rate /
2175: X_min_unit) * X_min_unit / X_base_min_unit)
2176: * X_base_min_unit) , 0)
2177: INTO X_result_fld
2178: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
2179: WHERE PLL.po_release_id = x_object_id
2180: AND PLL.line_location_id = POD.line_location_id
2181: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2182:
2220: nvl(pll.price_override,0))
2221: )
2222: ,x_precision))
2223: INTO x_result_fld
2224: FROM PO_LINE_LOCATIONS_ALL PLL
2225: WHERE PLL.po_release_id = x_object_id
2226: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET');
2227:
2228: else
2246: )
2247: /x_min_unit) *
2248: x_min_unit)
2249: INTO x_result_fld
2250: FROM PO_LINE_LOCATIONS_ALL PLL
2251: WHERE PLL.po_release_id = x_object_id
2252: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET');
2253:
2254: end if;
2292: * nvl(pll.price_override,0)
2293: )
2294: ),x_precision))
2295: INTO x_result_fld
2296: FROM PO_LINE_LOCATIONS_ALL PLL
2297: WHERE PLL.po_line_id = x_object_id
2298: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
2299:
2300: else
2315: * nvl(pll.price_override,0)
2316: )
2317: / x_min_unit) * x_min_unit))
2318: INTO x_result_fld
2319: FROM PO_LINE_LOCATIONS_ALL PLL
2320: WHERE PLL.po_line_id = x_object_id
2321: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
2322:
2323: end if;
2330: INTO x_min_unit,
2331: x_precision
2332: FROM FND_CURRENCIES C,
2333: PO_HEADERS_ALL PH,
2334: PO_LINE_LOCATIONS_ALL PLL
2335: WHERE PLL.line_location_id = x_object_id
2336: AND PH.po_header_id = PLL.po_header_id
2337: AND C.currency_code = PH.CURRENCY_CODE;
2338:
2356: * nvl(pll.price_override,0)
2357: )
2358: ),x_precision))
2359: INTO x_result_fld
2360: FROM PO_LINE_LOCATIONS_ALL PLL
2361: WHERE PLL.line_location_id = x_object_id;
2362:
2363: else
2364: x_progress := 244;
2375: * nvl(pll.price_override,0)
2376: )
2377: /x_min_unit) * x_min_unit))
2378: INTO x_result_fld
2379: FROM PO_LINE_LOCATIONS_ALL PLL
2380: WHERE PLL.line_location_id = x_object_id;
2381:
2382: end if;
2383:
2472: END IF; -- if l_base_currency <> l_po_currency
2473: l_progress := '050';
2474: --SQL WHAT- This query returns the total amount for an archived SPO
2475: --SQL WHY- To check if archived amount different from working copy amt. for a po in contract terms
2476: --SQL JOIN- Location id in PO_LINE_LOCATIONS_ARCHIVE_ALL and PO_DISTRIBUTIONS_ARCHIVE_ALL
2477: SELECT nvl(sum(round( ( (round( ( ( decode(POD.quantity_ordered, NULL,
2478: (nvl(POD.amount_ordered,0) -
2479: nvl(POD.amount_cancelled,0)
2480: ),
2498: nvl(l_base_min_unit,1)
2499: ), 0
2500: )
2501: INTO l_archive_total_amt
2502: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2503: WHERE PLL.po_header_id = p_object_id
2504: AND PLL.shipment_type in ('STANDARD')
2505: AND PLL.line_location_id = POD.line_location_id
2506: AND PLL.LATEST_EXTERNAL_FLAG = 'Y'
2582: END IF; -- if l_base_currency <> l_po_currency
2583: l_progress := '050';
2584: --SQL WHAT- This query returns the total amount for an archived SPO
2585: --SQL WHY- To derive the total amount for an archived PO revision
2586: --SQL JOIN- Location id in PO_LINE_LOCATIONS_ARCHIVE_ALL and PO_DISTRIBUTIONS_ARCHIVE_ALL
2587: SELECT nvl(sum(round( ( (round( ( ( decode(POD.quantity_ordered, NULL,
2588: (nvl(POD.amount_ordered,0) -
2589: nvl(POD.amount_cancelled,0)
2590: ),
2608: nvl(l_base_min_unit,1)
2609: ), 0
2610: )
2611: INTO l_archive_total_amt
2612: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2613: WHERE PLL.po_header_id = p_object_id
2614: AND PLL.shipment_type in ('STANDARD')
2615: AND PLL.line_location_id = POD.line_location_id
2616: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2612: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2613: WHERE PLL.po_header_id = p_object_id
2614: AND PLL.shipment_type in ('STANDARD')
2615: AND PLL.line_location_id = POD.line_location_id
2616: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2617: WHERE pll1.line_location_id = pll.line_location_id AND
2618: pll1.revision_num <= p_doc_revision)
2619: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2620: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2631: INTO l_min_unit,
2632: l_precision
2633: FROM FND_CURRENCIES C,
2634: PO_HEADERS_ALL PH,
2635: PO_LINE_LOCATIONS_ALL PLL
2636: WHERE PLL.line_location_id = p_object_id
2637: AND PH.po_header_id = PLL.po_header_id
2638: AND C.currency_code = PH.CURRENCY_CODE;
2639:
2655: * nvl(pll.price_override,0)
2656: )
2657: ),l_precision))
2658: INTO l_archive_total_amt
2659: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2660: WHERE PLL.line_location_id = p_object_id AND
2661: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2662: pll1.line_location_id = pll.line_location_id AND
2663: pll1.revision_num <= p_doc_revision) ;
2657: ),l_precision))
2658: INTO l_archive_total_amt
2659: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2660: WHERE PLL.line_location_id = p_object_id AND
2661: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2662: pll1.line_location_id = pll.line_location_id AND
2663: pll1.revision_num <= p_doc_revision) ;
2664:
2665: else
2676: * nvl(pll.price_override,0)
2677: )
2678: /l_min_unit) * l_min_unit))
2679: INTO l_archive_total_amt
2680: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2681: WHERE PLL.line_location_id = p_object_id AND
2682: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2683: pll1.line_location_id = pll.line_location_id AND
2684: pll1.revision_num <= p_doc_revision) ;
2678: /l_min_unit) * l_min_unit))
2679: INTO l_archive_total_amt
2680: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2681: WHERE PLL.line_location_id = p_object_id AND
2682: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2683: pll1.line_location_id = pll.line_location_id AND
2684: pll1.revision_num <= p_doc_revision) ;
2685:
2686: end if;
2714: * nvl(pll.price_override,0)
2715: )
2716: ),l_precision))
2717: INTO l_archive_total_amt
2718: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2719: WHERE PLL.po_line_id = p_object_id
2720: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2721: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2722: pll1.line_location_id = pll.line_location_id AND
2717: INTO l_archive_total_amt
2718: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2719: WHERE PLL.po_line_id = p_object_id
2720: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2721: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2722: pll1.line_location_id = pll.line_location_id AND
2723: pll1.revision_num <= p_doc_revision) ;
2724:
2725: else
2737: * nvl(pll.price_override,0)
2738: )
2739: / l_min_unit) * l_min_unit))
2740: INTO l_archive_total_amt
2741: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2742: WHERE PLL.po_line_id = p_object_id
2743: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2744: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2745: pll1.line_location_id = pll.line_location_id AND
2740: INTO l_archive_total_amt
2741: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2742: WHERE PLL.po_line_id = p_object_id
2743: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2744: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2745: pll1.line_location_id = pll.line_location_id AND
2746: pll1.revision_num <= p_doc_revision) ;
2747:
2748:
2800: )
2801: * POD.rate
2802: ,l_precision),l_base_precision)),0)
2803: INTO l_archive_total_amt
2804: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2805: WHERE PLL.po_release_id = p_object_id
2806: AND PLL.line_location_id = POD.line_location_id
2807: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2808: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2804: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2805: WHERE PLL.po_release_id = p_object_id
2806: AND PLL.line_location_id = POD.line_location_id
2807: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2808: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2809: WHERE pll1.line_location_id = pll.line_location_id AND
2810: pll1.revision_num <= p_doc_revision)
2811: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2812: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2828: )
2829: * POD.rate /
2830: l_min_unit) * l_min_unit , l_base_precision)),0)
2831: INTO l_archive_total_amt
2832: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2833: WHERE PLL.po_release_id = p_object_id
2834: AND PLL.line_location_id = POD.line_location_id
2835: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2836: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2832: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2833: WHERE PLL.po_release_id = p_object_id
2834: AND PLL.line_location_id = POD.line_location_id
2835: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2836: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2837: WHERE pll1.line_location_id = pll.line_location_id AND
2838: pll1.revision_num <= p_doc_revision)
2839: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2840: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2858: * POD.rate
2859: , l_precision)
2860: / l_base_min_unit ) * l_base_min_unit) ,0)
2861: INTO l_archive_total_amt
2862: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2863: WHERE PLL.po_release_id = p_object_id
2864: AND PLL.line_location_id = POD.line_location_id
2865: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2866: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2862: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2863: WHERE PLL.po_release_id = p_object_id
2864: AND PLL.line_location_id = POD.line_location_id
2865: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2866: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2867: WHERE pll1.line_location_id = pll.line_location_id AND
2868: pll1.revision_num <= p_doc_revision)
2869: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2870: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2886: * POD.rate /
2887: l_min_unit) * l_min_unit / l_base_min_unit)
2888: * l_base_min_unit) , 0)
2889: INTO l_archive_total_amt
2890: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2891: WHERE PLL.po_release_id = p_object_id
2892: AND PLL.line_location_id = POD.line_location_id
2893: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2894: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2890: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2891: WHERE PLL.po_release_id = p_object_id
2892: AND PLL.line_location_id = POD.line_location_id
2893: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2894: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2895: WHERE pll1.line_location_id = pll.line_location_id AND
2896: pll1.revision_num <= p_doc_revision)
2897: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2898: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2937: nvl(pll.price_override,0))
2938: )
2939: ,l_precision))
2940: INTO l_archive_total_amt
2941: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2942: WHERE PLL.po_release_id = p_object_id
2943: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2944: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2945: WHERE pll1.line_location_id = pll.line_location_id AND
2940: INTO l_archive_total_amt
2941: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2942: WHERE PLL.po_release_id = p_object_id
2943: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2944: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2945: WHERE pll1.line_location_id = pll.line_location_id AND
2946: pll1.revision_num <= p_doc_revision);
2947:
2948: else
2965: )
2966: /l_min_unit) *
2967: l_min_unit)
2968: INTO l_archive_total_amt
2969: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2970: WHERE PLL.po_release_id = p_object_id
2971: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2972: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2973: WHERE pll1.line_location_id = pll.line_location_id AND
2968: INTO l_archive_total_amt
2969: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2970: WHERE PLL.po_release_id = p_object_id
2971: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2972: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2973: WHERE pll1.line_location_id = pll.line_location_id AND
2974: pll1.revision_num <= p_doc_revision);
2975:
2976: end if;
3049: * nvl(pll.price_override,0)
3050: )
3051: ),x_precision))
3052: INTO x_result_fld
3053: FROM PO_LINE_LOCATIONS_ALL PLL
3054: WHERE PLL.po_line_id = p_line_id
3055: AND PLL.po_release_id = p_release_id
3056: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
3057:
3069: * nvl(pll.price_override,0)
3070: )
3071: / x_min_unit) * x_min_unit))
3072: INTO x_result_fld
3073: FROM PO_LINE_LOCATIONS_ALL PLL
3074: WHERE PLL.po_line_id = p_line_id
3075: AND PLL.po_release_id = p_release_id
3076: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
3077:
3166: * l_po_min_unit )
3167: / l_base_min_unit )
3168: * l_base_min_unit )
3169: INTO x_total
3170: FROM po_line_locations_all poll,
3171: po_lines_all pol
3172: WHERE poll.po_line_id = pol.po_line_id
3173: AND pol.from_header_id = p_po_header_id
3174: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3190: * l_rate )
3191: / l_base_min_unit )
3192: * l_base_min_unit )
3193: INTO x_total
3194: FROM po_line_locations_all poll,
3195: po_lines_all pol
3196: WHERE poll.po_line_id = pol.po_line_id
3197: AND pol.from_header_id = p_po_header_id
3198: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3219: * l_rate )
3220: / l_po_min_unit )
3221: * l_po_min_unit )
3222: INTO x_total
3223: FROM po_line_locations_all poll,
3224: po_lines_all pol
3225: WHERE poll.po_line_id = pol.po_line_id
3226: AND pol.from_header_id = p_po_header_id
3227: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3241: )
3242: )
3243: * l_rate )
3244: INTO x_total
3245: FROM po_line_locations_all poll,
3246: po_lines_all pol
3247: WHERE poll.po_line_id = pol.po_line_id
3248: AND pol.from_header_id = p_po_header_id
3249: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3270: )
3271: / l_po_min_unit )
3272: * l_po_min_unit )
3273: INTO x_total
3274: FROM po_line_locations_all poll,
3275: po_lines_all pol
3276: WHERE poll.po_line_id = pol.po_line_id
3277: AND pol.from_header_id = p_po_header_id
3278: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3292: * nvl(poll.price_override,0))
3293: )
3294: )
3295: INTO x_total
3296: FROM po_line_locations_all poll,
3297: po_lines_all pol
3298: WHERE poll.po_line_id = pol.po_line_id
3299: AND pol.from_header_id = p_po_header_id
3300: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3373: sum ( pod.quantity_ordered - pod.quantity_cancelled )
3374: INTO x_amount_released,
3375: x_quantity_released
3376: FROM po_distributions_all pod,
3377: po_line_locations_all poll,
3378: po_lines_all pol
3379: WHERE pod.line_location_id = poll.line_location_id
3380: AND poll.po_line_id = pol.po_line_id
3381: AND pol.from_line_id = p_po_line_id
3398: sum ( pod.quantity_ordered - pod.quantity_cancelled )
3399: INTO x_amount_released,
3400: x_quantity_released
3401: FROM po_distributions_all pod,
3402: po_line_locations_all poll,
3403: po_lines_all pol
3404: WHERE pod.line_location_id = poll.line_location_id
3405: AND poll.po_line_id = pol.po_line_id
3406: AND pol.from_line_id = p_po_line_id
3540: )
3541: ,0)
3542: INTO l_total
3543: FROM PO_DISTRIBUTIONS_ALL POD,
3544: PO_LINE_LOCATIONS_ALL POLL,
3545: PO_LINES_ALL POL,
3546: PO_HEADERS_ALL PH1
3547: WHERE POL.contract_id = p_po_header_id
3548: AND POL.po_line_id = POLL.po_line_id
3608: )
3609: ,0)
3610: INTO l_total
3611: FROM PO_DISTRIBUTIONS_ALL POD,
3612: PO_LINE_LOCATIONS_ALL POLL,
3613: PO_LINES_ALL POL,
3614: PO_HEADERS_ALL PH1
3615: WHERE POL.contract_id = p_po_header_id
3616: AND POL.po_line_id = POLL.po_line_id
3677: )
3678: ),0)
3679: INTO l_total
3680: FROM PO_DISTRIBUTIONS_ALL POD,
3681: PO_LINE_LOCATIONS_ALL POLL,
3682: PO_LINES_ALL PoL,
3683: PO_HEADERS_ALL PH1
3684: WHERE POL.contract_id = p_po_header_id
3685: AND POL.po_line_id = POLL.po_line_id
3742: )
3743: ),0)
3744: INTO l_total
3745: FROM PO_DISTRIBUTIONS_ALL POD,
3746: PO_LINE_LOCATIONS_ALL POLL,
3747: PO_LINES_ALL POL,
3748: PO_HEADERS_ALL PH1
3749: WHERE POL.contract_id = p_po_header_id
3750: AND POL.po_line_id = POLL.po_line_id
3773: --Also added a join with PO_HEADERS_ALL for rate in header of execution document
3774: --
3775: l_progress := '100';
3776: -- Bug# 13536418: Added hint to improve performance.
3777: SELECT /*+ use_nl(POLL,POD,PH1) index(POLL PO_LINE_LOCATIONS_N1) index(PH1 PO_HEADERS_U1) */ NVL(
3778: SUM(
3779: DECODE(PH.currency_code, PH1.currency_code,
3780: ROUND (
3781: DECODE(POLL.value_basis
3813: )
3814: ,0)
3815: INTO l_total
3816: FROM PO_DISTRIBUTIONS_ALL POD,
3817: PO_LINE_LOCATIONS_ALL POLL,
3818: PO_LINES_ALL POL,
3819: PO_HEADERS_ALL PH,
3820: PO_HEADERS_ALL PH1
3821: WHERE PH.po_header_id = p_po_header_id
3838: --Also added a join with PO_HEADERS_ALL for rate in header of execution document
3839: --
3840: l_progress := '110';
3841: -- Bug# 13536418: Added hint to improve performance.
3842: SELECT /*+ use_nl(POLL,POD,PH1) index(POLL PO_LINE_LOCATIONS_N1) index(PH1 PO_HEADERS_U1) */ NVL(
3843: SUM(
3844:
3845: DECODE(PH.currency_code, PH1.currency_code,
3846: ROUND(
3882: )
3883: ,0)
3884: INTO l_total
3885: FROM PO_DISTRIBUTIONS_ALL POD,
3886: PO_LINE_LOCATIONS_ALL POLL,
3887: PO_LINES_ALL POL,
3888: PO_HEADERS_ALL PH,
3889: PO_HEADERS_ALL PH1
3890: WHERE PH.po_header_id = p_po_header_id
4268: SELECT DECODE( p_doc_type
4269: , g_doc_type_RELEASE, POLL.po_release_id
4270: , POLL.po_header_id
4271: )
4272: FROM PO_LINE_LOCATIONS_ALL POLL
4273: WHERE POLL.line_location_id = p_doc_level_id_tbl(i)
4274: )
4275: WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
4276: RETURNING num2
4558: UPDATE PO_SESSION_GT
4559: SET num2 =
4560: (
4561: SELECT POLL.po_line_id
4562: FROM PO_LINE_LOCATIONS_MERGE_V POLL --
4564: AND POLL.draft_id = p_draft_id
4565: )
4566: WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
4835:
4836: SELECT POLL.line_location_id
4837: BULK COLLECT INTO x_line_location_id_tbl
4838: FROM
4839: PO_LINE_LOCATIONS_ALL POLL
4840: , PO_SESSION_GT IDS
4841: WHERE POLL.po_release_id = IDS.num1
4842: AND IDS.key = l_doc_level_id_key
4843: ;
4853:
4854: SELECT POLL.line_location_id
4855: BULK COLLECT INTO x_line_location_id_tbl
4856: FROM
4857: PO_LINE_LOCATIONS_MERGE_V POLL --
4859: WHERE POLL.po_header_id = IDS.num1
4860: and POLL.draft_id = p_draft_id
4861: AND POLL.shipment_type <> g_ship_type_SCHEDULED
4875:
4876: SELECT POLL.line_location_id
4877: BULK COLLECT INTO x_line_location_id_tbl
4878: FROM
4879: PO_LINE_LOCATIONS_MERGE_V POLL --
4881: WHERE POLL.po_line_id = IDS.num1
4882: and POLL.draft_id = p_draft_id
4883: AND POLL.shipment_type <> g_ship_type_SCHEDULED
6614: , x_prevented_count
6615:
6616: FROM
6617: PO_DISTRIBUTIONS_ALL POD
6618: , PO_LINE_LOCATIONS_ALL POLL
6619: , PO_HEADERS_ALL POH
6620:
6621: WHERE POLL.line_location_id(+) = POD.line_location_id
6622: AND POH.po_header_id = POD.po_header_id
7561: , poh.rate_type
7562: , poll.price_discount
7563: , poll.price_override
7564: , DECODE(poll.line_location_id, NULL, pol.unit_meas_lookup_code, poll.unit_meas_lookup_code)
7565: FROM po_headers poh, po_lines pol, po_line_locations poll
7566: WHERE poh.po_header_id = p_header_id
7567: AND poh.po_header_id = pol.po_header_id
7568: AND pol.line_num = p_line_num
7569: AND pol.po_line_id = poll.po_line_id (+)
7599: 'QUOTATION', DECODE(poll.ship_to_location_id,
7600: NULL,poll.price_override, pol.unit_price),
7601: pol.unit_price)
7602: , DECODE(poll.line_location_id, NULL, pol.unit_meas_lookup_code, poll.unit_meas_lookup_code)
7603: FROM po_headers poh, po_lines pol, po_line_locations poll
7604: WHERE poh.po_header_id = p_header_id
7605: AND poh.po_header_id = pol.po_header_id
7606: AND pol.line_num = p_line_num
7607: AND pol.po_line_id = poll.po_line_id (+)
8031: FUNCTION get_last_update_date_for_doc(p_doc_header_id IN NUMBER) RETURN DATE
8032: IS
8033: header_last_update_date PO_HEADERS_ALL.last_update_date%type;
8034: line_last_update_date PO_LINES_ALL.last_update_date%type;
8035: line_loc_last_update_date PO_LINE_LOCATIONS_ALL.last_update_date%type;
8036: dist_last_update_date PO_DISTRIBUTIONS_ALL.last_update_date%type;
8037: doc_last_update_date PO_HEADERS_ALL.last_update_date%type;
8038: --Lowest date supported by database as we need to handle null dates
8039: min_date PO_HEADERS_ALL.last_update_date%type := to_date('01/01/-4712','DD/MM/SYYYY');
8050: select nvl(max(last_update_date), min_date) into header_last_update_date from po_headers_all where po_header_id = p_doc_header_id;
8051: d_pos := 20;
8052: select nvl(max(last_update_date), min_date) into line_last_update_date from po_lines_all where po_header_id = p_doc_header_id;
8053: d_pos := 30;
8054: select nvl(max(last_update_date), min_date) into line_loc_last_update_date from po_line_locations_all where po_header_id = p_doc_header_id;
8055: d_pos := 40;
8056: select nvl(max(last_update_date), min_date) into dist_last_update_date from po_distributions_all where po_header_id = p_doc_header_id;
8057: d_pos := 50;
8058: doc_last_update_date := greatest( header_last_update_date
8250: IS
8251: draft_last_update_date PO_DRAFTS.last_update_date%type;
8252: header_last_update_date PO_HEADERS_DRAFT_ALL.last_update_date%type;
8253: line_last_update_date PO_LINES_DRAFT_ALL.last_update_date%type;
8254: line_loc_last_update_date PO_LINE_LOCATIONS_DRAFT_ALL.last_update_date%type;
8255: dist_last_update_date PO_DISTRIBUTIONS_DRAFT_ALL.last_update_date%type;
8256: mod_last_update_date PO_HEADERS_DRAFT_ALL.last_update_date%type;
8257: address_last_update_date po_headers_all_ext_b.last_update_date%type;
8258: org_assign_last_update_date po_ga_org_assign_draft.last_update_date%type;
8271: select nvl(max(last_update_date), min_date) into header_last_update_date from po_headers_draft_all where draft_id = p_doc_id;
8272: d_pos := 20;
8273: select nvl(max(last_update_date), min_date) into line_last_update_date from po_lines_draft_all where draft_id = p_doc_id;
8274: d_pos := 30;
8275: select nvl(max(last_update_date), min_date) into line_loc_last_update_date from po_line_locations_draft_all where draft_id = p_doc_id;
8276: d_pos := 40;
8277: select nvl(max(last_update_date), min_date) into dist_last_update_date from po_distributions_draft_all where draft_id = p_doc_id;
8278: d_pos := 50;
8279: select nvl(max(last_update_date), min_date) into draft_last_update_date from po_drafts where draft_id = p_doc_id;