[Home] [Help]
1251: )
1252: , X_precision) ,
1253: X_base_precision)),0)
1254: INTO X_result_fld
1255: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1256: WHERE PLL.po_header_id = X_object_id
1257: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1258: AND PLL.line_location_id = POD.line_location_id;
1259:
1278: * POD.rate / X_min_unit
1279: )
1280: * X_min_unit , X_base_precision)),0)
1281: INTO X_result_fld
1282: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1283: WHERE PLL.po_header_id = X_object_id
1284: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1285: AND PLL.line_location_id = POD.line_location_id;
1286:
1307: )
1308: * POD.rate , X_precision)
1309: / X_base_min_unit ) * X_base_min_unit) ,0)
1310: INTO X_result_fld
1311: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1312: WHERE PLL.po_header_id = X_object_id
1313: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1314: AND PLL.line_location_id = POD.line_location_id;
1315:
1333: * POD.rate /
1334: X_min_unit) * X_min_unit / X_base_min_unit)
1335: * X_base_min_unit) , 0)
1336: INTO X_result_fld
1337: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1338: WHERE PLL.po_header_id = X_object_id
1339: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET')
1340: AND PLL.line_location_id = POD.line_location_id;
1341:
1386: * nvl(pll.price_override,0)
1387: )
1388: ,x_precision))
1389: INTO x_result_fld
1390: FROM PO_LINE_LOCATIONS_ALL PLL
1391: WHERE PLL.po_header_id = x_object_id
1392: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
1393:
1394: else
1412: )
1413: / x_min_unit)
1414: * x_min_unit)
1415: INTO x_result_fld
1416: FROM PO_LINE_LOCATIONS_ALL PLL
1417: WHERE PLL.po_header_id = x_object_id
1418: AND PLL.shipment_type in ('STANDARD','PLANNED','BLANKET');
1419:
1420: end if;
1499: nvl(POD.quantity_cancelled, 0)) *
1500: nvl(PLL.price_override, 0) * POD.rate, X_precision) ,
1501: X_base_precision)),0)
1502: INTO X_result_fld
1503: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1504: WHERE PLL.po_header_id = X_object_id
1505: -- Bugs 482497 and 602664, lpo, 12/22/97
1506: AND PLL.shipment_type = 'SCHEDULED'
1507: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1514: nvl(POD.quantity_cancelled, 0)) *
1515: nvl(PLL.price_override, 0) * POD.rate /
1516: X_min_unit) * X_min_unit , 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
1532: nvl(POD.quantity_cancelled, 0)) *
1533: nvl(PLL.price_override, 0) * POD.rate , X_precision)
1534: / X_base_min_unit ) * X_base_min_unit) ,0)
1535: INTO X_result_fld
1536: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1537: WHERE PLL.po_header_id = X_object_id
1538: -- Bugs 482497 and 602664, lpo, 12/22/97
1539: AND PLL.shipment_type = 'SCHEDULED'
1540: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1548: nvl(PLL.price_override, 0) * POD.rate /
1549: X_min_unit) * X_min_unit / X_base_min_unit)
1550: * X_base_min_unit) , 0)
1551: INTO X_result_fld
1552: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1553: WHERE PLL.po_header_id = X_object_id
1554: -- Bugs 482497 and 602664, lpo, 12/22/97
1555: AND PLL.shipment_type = 'SCHEDULED'
1556: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1588: --< Bug 3549096 > Use _ALL tables instead of org-striped views.
1589: select sum(round((pll.quantity - nvl(pll.quantity_cancelled,0))*
1590: nvl(pll.price_override,0),x_precision))
1591: INTO x_result_fld
1592: FROM PO_LINE_LOCATIONS_ALL PLL
1593: WHERE PLL.po_header_id = x_object_id
1594: -- Bugs 482497 and 602664, lpo, 12/22/97
1595: AND PLL.shipment_type = 'SCHEDULED';
1596: -- Bugs 482497 and 602664, lpo, 12/22/97
1605: nvl(pll.quantity_cancelled,0)) *
1606: nvl(pll.price_override,0)/x_min_unit)*
1607: x_min_unit)
1608: INTO x_result_fld
1609: FROM PO_LINE_LOCATIONS_ALL PLL
1610: WHERE PLL.po_header_id = x_object_id
1611: -- Bugs 482497 and 602664, lpo, 12/22/97
1612: AND PLL.shipment_type = 'SCHEDULED';
1613: -- End of fix. Bugs 482497 and 602664, lpo, 12/22/97
1835: functional currency and then changing this to the Contract currency */
1836:
1837: /* 716188 - Added an outer join on PO_DISTRIBUTIONS */
1838: /* 866358 - BPESCHAN: Changed the sql stmt to select quantity_ordered and
1839: quantity_cancelled from PO_DISTRIBUTIONS instead of PO_LINE_LOCATIONS.
1840: This fix prevents incorrect calculation for amount release when more then
1841: one distribution exists. */
1842:
1843: /* 958792 kbenjami 8/25/99. Proprogated fix from R11.
1867: * nvl(POD.rate, nvl(PH1.rate,1))/nvl(PH.rate,1),x_precision)
1868: )),0)
1869: INTO x_result_fld
1870: FROM PO_DISTRIBUTIONS_ALL POD,
1871: PO_LINE_LOCATIONS_ALL PLL,
1872: PO_LINES_ALL PL,
1873: PO_HEADERS_ALL PH,
1874: PO_HEADERS_ALL PH1
1875: --,FND_CURRENCIES C
1901: * nvl(POD.rate, nvl(PH1.rate,1))/nvl(PH.rate,1)/x_min_unit))
1902: * x_min_unit),0)
1903: INTO x_result_fld
1904: FROM PO_DISTRIBUTIONS_ALL POD,
1905: PO_LINE_LOCATIONS_ALL PLL,
1906: PO_LINES_ALL PL,
1907: PO_HEADERS_ALL PH,
1908: PO_HEADERS_ALL PH1
1909: --,FND_CURRENCIES C
1972: )
1973: * POD.rate
1974: ,x_precision),x_base_precision)),0)
1975: INTO x_result_fld
1976: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1977: WHERE PLL.po_release_id = x_object_id
1978: AND PLL.line_location_id = POD.line_location_id
1979: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
1980:
1994: )
1995: * POD.rate /
1996: X_min_unit) * X_min_unit , X_base_precision)),0)
1997: INTO x_result_fld
1998: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
1999: WHERE PLL.po_release_id = x_object_id
2000: AND PLL.line_location_id = POD.line_location_id
2001: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2002:
2019: * POD.rate
2020: , X_precision)
2021: / X_base_min_unit ) * X_base_min_unit) ,0)
2022: INTO X_result_fld
2023: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
2024: WHERE PLL.po_release_id = x_object_id
2025: AND PLL.line_location_id = POD.line_location_id
2026: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2027:
2042: * POD.rate /
2043: X_min_unit) * X_min_unit / X_base_min_unit)
2044: * X_base_min_unit) , 0)
2045: INTO X_result_fld
2046: FROM PO_DISTRIBUTIONS_ALL POD, PO_LINE_LOCATIONS_ALL PLL
2047: WHERE PLL.po_release_id = x_object_id
2048: AND PLL.line_location_id = POD.line_location_id
2049: AND PLL.shipment_type in ('SCHEDULED','BLANKET');
2050:
2088: nvl(pll.price_override,0))
2089: )
2090: ,x_precision))
2091: INTO x_result_fld
2092: FROM PO_LINE_LOCATIONS_ALL PLL
2093: WHERE PLL.po_release_id = x_object_id
2094: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET');
2095:
2096: else
2114: )
2115: /x_min_unit) *
2116: x_min_unit)
2117: INTO x_result_fld
2118: FROM PO_LINE_LOCATIONS_ALL PLL
2119: WHERE PLL.po_release_id = x_object_id
2120: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET');
2121:
2122: end if;
2160: * nvl(pll.price_override,0)
2161: )
2162: ),x_precision))
2163: INTO x_result_fld
2164: FROM PO_LINE_LOCATIONS_ALL PLL
2165: WHERE PLL.po_line_id = x_object_id
2166: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
2167:
2168: else
2183: * nvl(pll.price_override,0)
2184: )
2185: / x_min_unit) * x_min_unit))
2186: INTO x_result_fld
2187: FROM PO_LINE_LOCATIONS_ALL PLL
2188: WHERE PLL.po_line_id = x_object_id
2189: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
2190:
2191: end if;
2198: INTO x_min_unit,
2199: x_precision
2200: FROM FND_CURRENCIES C,
2201: PO_HEADERS_ALL PH,
2202: PO_LINE_LOCATIONS_ALL PLL
2203: WHERE PLL.line_location_id = x_object_id
2204: AND PH.po_header_id = PLL.po_header_id
2205: AND C.currency_code = PH.CURRENCY_CODE;
2206:
2224: * nvl(pll.price_override,0)
2225: )
2226: ),x_precision))
2227: INTO x_result_fld
2228: FROM PO_LINE_LOCATIONS_ALL PLL
2229: WHERE PLL.line_location_id = x_object_id;
2230:
2231: else
2232: x_progress := 244;
2243: * nvl(pll.price_override,0)
2244: )
2245: /x_min_unit) * x_min_unit))
2246: INTO x_result_fld
2247: FROM PO_LINE_LOCATIONS_ALL PLL
2248: WHERE PLL.line_location_id = x_object_id;
2249:
2250: end if;
2251:
2340: END IF; -- if l_base_currency <> l_po_currency
2341: l_progress := '050';
2342: --SQL WHAT- This query returns the total amount for an archived SPO
2343: --SQL WHY- To check if archived amount different from working copy amt. for a po in contract terms
2344: --SQL JOIN- Location id in PO_LINE_LOCATIONS_ARCHIVE_ALL and PO_DISTRIBUTIONS_ARCHIVE_ALL
2345: SELECT nvl(sum(round( ( (round( ( ( decode(POD.quantity_ordered, NULL,
2346: (nvl(POD.amount_ordered,0) -
2347: nvl(POD.amount_cancelled,0)
2348: ),
2366: nvl(l_base_min_unit,1)
2367: ), 0
2368: )
2369: INTO l_archive_total_amt
2370: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2371: WHERE PLL.po_header_id = p_object_id
2372: AND PLL.shipment_type in ('STANDARD')
2373: AND PLL.line_location_id = POD.line_location_id
2374: AND PLL.LATEST_EXTERNAL_FLAG = 'Y'
2450: END IF; -- if l_base_currency <> l_po_currency
2451: l_progress := '050';
2452: --SQL WHAT- This query returns the total amount for an archived SPO
2453: --SQL WHY- To derive the total amount for an archived PO revision
2454: --SQL JOIN- Location id in PO_LINE_LOCATIONS_ARCHIVE_ALL and PO_DISTRIBUTIONS_ARCHIVE_ALL
2455: SELECT nvl(sum(round( ( (round( ( ( decode(POD.quantity_ordered, NULL,
2456: (nvl(POD.amount_ordered,0) -
2457: nvl(POD.amount_cancelled,0)
2458: ),
2476: nvl(l_base_min_unit,1)
2477: ), 0
2478: )
2479: INTO l_archive_total_amt
2480: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2481: WHERE PLL.po_header_id = p_object_id
2482: AND PLL.shipment_type in ('STANDARD')
2483: AND PLL.line_location_id = POD.line_location_id
2484: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2480: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2481: WHERE PLL.po_header_id = p_object_id
2482: AND PLL.shipment_type in ('STANDARD')
2483: AND PLL.line_location_id = POD.line_location_id
2484: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2485: WHERE pll1.line_location_id = pll.line_location_id AND
2486: pll1.revision_num <= p_doc_revision)
2487: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2488: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2499: INTO l_min_unit,
2500: l_precision
2501: FROM FND_CURRENCIES C,
2502: PO_HEADERS_ALL PH,
2503: PO_LINE_LOCATIONS_ALL PLL
2504: WHERE PLL.line_location_id = p_object_id
2505: AND PH.po_header_id = PLL.po_header_id
2506: AND C.currency_code = PH.CURRENCY_CODE;
2507:
2523: * nvl(pll.price_override,0)
2524: )
2525: ),l_precision))
2526: INTO l_archive_total_amt
2527: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2528: WHERE PLL.line_location_id = p_object_id AND
2529: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2530: pll1.line_location_id = pll.line_location_id AND
2531: pll1.revision_num <= p_doc_revision) ;
2525: ),l_precision))
2526: INTO l_archive_total_amt
2527: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2528: WHERE PLL.line_location_id = p_object_id AND
2529: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2530: pll1.line_location_id = pll.line_location_id AND
2531: pll1.revision_num <= p_doc_revision) ;
2532:
2533: else
2544: * nvl(pll.price_override,0)
2545: )
2546: /l_min_unit) * l_min_unit))
2547: INTO l_archive_total_amt
2548: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2549: WHERE PLL.line_location_id = p_object_id AND
2550: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2551: pll1.line_location_id = pll.line_location_id AND
2552: pll1.revision_num <= p_doc_revision) ;
2546: /l_min_unit) * l_min_unit))
2547: INTO l_archive_total_amt
2548: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2549: WHERE PLL.line_location_id = p_object_id AND
2550: PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2551: pll1.line_location_id = pll.line_location_id AND
2552: pll1.revision_num <= p_doc_revision) ;
2553:
2554: end if;
2582: * nvl(pll.price_override,0)
2583: )
2584: ),l_precision))
2585: INTO l_archive_total_amt
2586: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2587: WHERE PLL.po_line_id = p_object_id
2588: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2589: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2590: pll1.line_location_id = pll.line_location_id AND
2585: INTO l_archive_total_amt
2586: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2587: WHERE PLL.po_line_id = p_object_id
2588: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2589: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2590: pll1.line_location_id = pll.line_location_id AND
2591: pll1.revision_num <= p_doc_revision) ;
2592:
2593: else
2605: * nvl(pll.price_override,0)
2606: )
2607: / l_min_unit) * l_min_unit))
2608: INTO l_archive_total_amt
2609: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2610: WHERE PLL.po_line_id = p_object_id
2611: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2612: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2613: pll1.line_location_id = pll.line_location_id AND
2608: INTO l_archive_total_amt
2609: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2610: WHERE PLL.po_line_id = p_object_id
2611: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED')
2612: AND PLL.revision_num = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1 WHERE
2613: pll1.line_location_id = pll.line_location_id AND
2614: pll1.revision_num <= p_doc_revision) ;
2615:
2616:
2668: )
2669: * POD.rate
2670: ,l_precision),l_base_precision)),0)
2671: INTO l_archive_total_amt
2672: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2673: WHERE PLL.po_release_id = p_object_id
2674: AND PLL.line_location_id = POD.line_location_id
2675: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2676: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2672: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2673: WHERE PLL.po_release_id = p_object_id
2674: AND PLL.line_location_id = POD.line_location_id
2675: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2676: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2677: WHERE pll1.line_location_id = pll.line_location_id AND
2678: pll1.revision_num <= p_doc_revision)
2679: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2680: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2696: )
2697: * POD.rate /
2698: l_min_unit) * l_min_unit , l_base_precision)),0)
2699: INTO l_archive_total_amt
2700: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2701: WHERE PLL.po_release_id = p_object_id
2702: AND PLL.line_location_id = POD.line_location_id
2703: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2704: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2700: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2701: WHERE PLL.po_release_id = p_object_id
2702: AND PLL.line_location_id = POD.line_location_id
2703: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2704: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2705: WHERE pll1.line_location_id = pll.line_location_id AND
2706: pll1.revision_num <= p_doc_revision)
2707: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2708: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2726: * POD.rate
2727: , l_precision)
2728: / l_base_min_unit ) * l_base_min_unit) ,0)
2729: INTO l_archive_total_amt
2730: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2731: WHERE PLL.po_release_id = p_object_id
2732: AND PLL.line_location_id = POD.line_location_id
2733: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2734: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2730: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2731: WHERE PLL.po_release_id = p_object_id
2732: AND PLL.line_location_id = POD.line_location_id
2733: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2734: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2735: WHERE pll1.line_location_id = pll.line_location_id AND
2736: pll1.revision_num <= p_doc_revision)
2737: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2738: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2754: * POD.rate /
2755: l_min_unit) * l_min_unit / l_base_min_unit)
2756: * l_base_min_unit) , 0)
2757: INTO l_archive_total_amt
2758: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2759: WHERE PLL.po_release_id = p_object_id
2760: AND PLL.line_location_id = POD.line_location_id
2761: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2762: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2758: FROM PO_DISTRIBUTIONS_ARCHIVE_ALL POD, PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2759: WHERE PLL.po_release_id = p_object_id
2760: AND PLL.line_location_id = POD.line_location_id
2761: AND PLL.shipment_type in ('SCHEDULED','BLANKET')
2762: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2763: WHERE pll1.line_location_id = pll.line_location_id AND
2764: pll1.revision_num <= p_doc_revision)
2765: AND POD.REVISION_NUM = (SELECT MAX(pdd1.revision_num) FROM PO_DISTRIBUTIONS_ARCHIVE_ALL PDD1
2766: WHERE pdd1.po_distribution_id = pod.po_distribution_id AND
2805: nvl(pll.price_override,0))
2806: )
2807: ,l_precision))
2808: INTO l_archive_total_amt
2809: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2810: WHERE PLL.po_release_id = p_object_id
2811: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2812: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2813: WHERE pll1.line_location_id = pll.line_location_id AND
2808: INTO l_archive_total_amt
2809: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2810: WHERE PLL.po_release_id = p_object_id
2811: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2812: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2813: WHERE pll1.line_location_id = pll.line_location_id AND
2814: pll1.revision_num <= p_doc_revision);
2815:
2816: else
2833: )
2834: /l_min_unit) *
2835: l_min_unit)
2836: INTO l_archive_total_amt
2837: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2838: WHERE PLL.po_release_id = p_object_id
2839: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2840: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2841: WHERE pll1.line_location_id = pll.line_location_id AND
2836: INTO l_archive_total_amt
2837: FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL
2838: WHERE PLL.po_release_id = p_object_id
2839: AND PLL.shipment_type in ( 'SCHEDULED','BLANKET')
2840: AND PLL.REVISION_NUM = (SELECT MAX(pll1.revision_num) FROM PO_LINE_LOCATIONS_ARCHIVE_ALL PLL1
2841: WHERE pll1.line_location_id = pll.line_location_id AND
2842: pll1.revision_num <= p_doc_revision);
2843:
2844: end if;
2917: * nvl(pll.price_override,0)
2918: )
2919: ),x_precision))
2920: INTO x_result_fld
2921: FROM PO_LINE_LOCATIONS_ALL PLL
2922: WHERE PLL.po_line_id = p_line_id
2923: AND PLL.po_release_id = p_release_id
2924: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
2925:
2937: * nvl(pll.price_override,0)
2938: )
2939: / x_min_unit) * x_min_unit))
2940: INTO x_result_fld
2941: FROM PO_LINE_LOCATIONS_ALL PLL
2942: WHERE PLL.po_line_id = p_line_id
2943: AND PLL.po_release_id = p_release_id
2944: AND PLL.shipment_type in ( 'STANDARD','BLANKET','PLANNED');
2945:
3034: / l_base_min_unit )
3035: * l_base_min_unit )
3036: INTO x_total
3037: FROM po_distributions_all pod,
3038: po_line_locations_all poll,
3039: po_lines_all pol
3040: WHERE pod.line_location_id = poll.line_location_id
3041: AND poll.po_line_id = pol.po_line_id
3042: AND pol.from_header_id = p_po_header_id;
3059: / l_base_min_unit )
3060: * l_base_min_unit )
3061: INTO x_total
3062: FROM po_distributions_all pod,
3063: po_line_locations_all poll,
3064: po_lines_all pol
3065: WHERE pod.line_location_id = poll.line_location_id
3066: AND poll.po_line_id = pol.po_line_id
3067: AND pol.from_header_id = p_po_header_id;
3088: / l_po_min_unit )
3089: * l_po_min_unit )
3090: INTO x_total
3091: FROM po_distributions_all pod,
3092: po_line_locations_all poll,
3093: po_lines_all pol
3094: WHERE pod.line_location_id = poll.line_location_id
3095: AND poll.po_line_id = pol.po_line_id
3096: AND pol.from_header_id = p_po_header_id;
3111: )
3112: * l_rate )
3113: INTO x_total
3114: FROM po_distributions_all pod,
3115: po_line_locations_all poll,
3116: po_lines_all pol
3117: WHERE pod.line_location_id = poll.line_location_id
3118: AND poll.po_line_id = pol.po_line_id
3119: AND pol.from_header_id = p_po_header_id;
3141: / l_po_min_unit )
3142: * l_po_min_unit )
3143: INTO x_total
3144: FROM po_distributions_all pod,
3145: po_line_locations_all poll,
3146: po_lines_all pol
3147: WHERE pod.line_location_id = poll.line_location_id
3148: AND poll.po_line_id = pol.po_line_id
3149: AND pol.from_header_id = p_po_header_id;
3163: )
3164: )
3165: INTO x_total
3166: FROM po_distributions_all pod,
3167: po_line_locations_all poll,
3168: po_lines_all pol
3169: WHERE pod.line_location_id = poll.line_location_id
3170: AND poll.po_line_id = pol.po_line_id
3171: AND pol.from_header_id = p_po_header_id;
3241: sum ( pod.quantity_ordered - pod.quantity_cancelled )
3242: INTO x_amount_released,
3243: x_quantity_released
3244: FROM po_distributions_all pod,
3245: po_line_locations_all poll,
3246: po_lines_all pol
3247: WHERE pod.line_location_id = poll.line_location_id
3248: AND poll.po_line_id = pol.po_line_id
3249: AND pol.from_line_id = p_po_line_id;
3265: sum ( pod.quantity_ordered - pod.quantity_cancelled )
3266: INTO x_amount_released,
3267: x_quantity_released
3268: FROM po_distributions_all pod,
3269: po_line_locations_all poll,
3270: po_lines_all pol
3271: WHERE pod.line_location_id = poll.line_location_id
3272: AND poll.po_line_id = pol.po_line_id
3273: AND pol.from_line_id = p_po_line_id;
3403: )
3404: ,0)
3405: INTO l_total
3406: FROM PO_DISTRIBUTIONS_ALL POD,
3407: PO_LINE_LOCATIONS_ALL POLL,
3408: PO_LINES_ALL POL,
3409: PO_HEADERS_ALL PH1
3410: WHERE POL.contract_id = p_po_header_id
3411: AND POL.po_line_id = POLL.po_line_id
3469: )
3470: ,0)
3471: INTO l_total
3472: FROM PO_DISTRIBUTIONS_ALL POD,
3473: PO_LINE_LOCATIONS_ALL POLL,
3474: PO_LINES_ALL POL,
3475: PO_HEADERS_ALL PH1
3476: WHERE POL.contract_id = p_po_header_id
3477: AND POL.po_line_id = POLL.po_line_id
3536: )
3537: ),0)
3538: INTO l_total
3539: FROM PO_DISTRIBUTIONS_ALL POD,
3540: PO_LINE_LOCATIONS_ALL POLL,
3541: PO_LINES_ALL PoL,
3542: PO_HEADERS_ALL PH1
3543: WHERE POL.contract_id = p_po_header_id
3544: AND POL.po_line_id = POLL.po_line_id
3599: )
3600: ),0)
3601: INTO l_total
3602: FROM PO_DISTRIBUTIONS_ALL POD,
3603: PO_LINE_LOCATIONS_ALL POLL,
3604: PO_LINES_ALL POL,
3605: PO_HEADERS_ALL PH1
3606: WHERE POL.contract_id = p_po_header_id
3607: AND POL.po_line_id = POLL.po_line_id
3667: )
3668: ,0)
3669: INTO l_total
3670: FROM PO_DISTRIBUTIONS_ALL POD,
3671: PO_LINE_LOCATIONS_ALL POLL,
3672: PO_LINES_ALL POL,
3673: PO_HEADERS_ALL PH,
3674: PO_HEADERS_ALL PH1
3675: WHERE PH.po_header_id = p_po_header_id
3734: )
3735: ,0)
3736: INTO l_total
3737: FROM PO_DISTRIBUTIONS_ALL POD,
3738: PO_LINE_LOCATIONS_ALL POLL,
3739: PO_LINES_ALL POL,
3740: PO_HEADERS_ALL PH,
3741: PO_HEADERS_ALL PH1
3742: WHERE PH.po_header_id = p_po_header_id
4118: SELECT DECODE( p_doc_type
4119: , g_doc_type_RELEASE, POLL.po_release_id
4120: , POLL.po_header_id
4121: )
4122: FROM PO_LINE_LOCATIONS_ALL POLL
4123: WHERE POLL.line_location_id = p_doc_level_id_tbl(i)
4124: )
4125: WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
4126: RETURNING num2
4391: UPDATE PO_SESSION_GT
4392: SET num2 =
4393: (
4394: SELECT POLL.po_line_id
4395: FROM PO_LINE_LOCATIONS_ALL POLL
4396: WHERE POLL.line_location_id = p_doc_level_id_tbl(i)
4397: )
4398: WHERE rowid = CHARTOROWID(l_rowid_char_tbl(i))
4399: RETURNING num2
4649:
4650: SELECT POLL.line_location_id
4651: BULK COLLECT INTO x_line_location_id_tbl
4652: FROM
4653: PO_LINE_LOCATIONS_ALL POLL
4654: , PO_SESSION_GT IDS
4655: WHERE POLL.po_release_id = IDS.num1
4656: AND IDS.key = l_doc_level_id_key
4657: ;
4667:
4668: SELECT POLL.line_location_id
4669: BULK COLLECT INTO x_line_location_id_tbl
4670: FROM
4671: PO_LINE_LOCATIONS_ALL POLL
4672: , PO_SESSION_GT IDS
4673: WHERE POLL.po_header_id = IDS.num1
4674: AND POLL.shipment_type <> g_ship_type_SCHEDULED
4675: AND POLL.shipment_type <> g_ship_type_BLANKET
4688:
4689: SELECT POLL.line_location_id
4690: BULK COLLECT INTO x_line_location_id_tbl
4691: FROM
4692: PO_LINE_LOCATIONS_ALL POLL
4693: , PO_SESSION_GT IDS
4694: WHERE POLL.po_line_id = IDS.num1
4695: AND POLL.shipment_type <> g_ship_type_SCHEDULED
4696: AND POLL.shipment_type <> g_ship_type_BLANKET
6406: , x_prevented_count
6407:
6408: FROM
6409: PO_DISTRIBUTIONS_ALL POD
6410: , PO_LINE_LOCATIONS_ALL POLL
6411: , PO_HEADERS_ALL POH
6412:
6413: WHERE POLL.line_location_id(+) = POD.line_location_id
6414: AND POH.po_header_id = POD.po_header_id
7353: , poh.rate_type
7354: , poll.price_discount
7355: , poll.price_override
7356: , DECODE(poll.line_location_id, NULL, pol.unit_meas_lookup_code, poll.unit_meas_lookup_code)
7357: FROM po_headers poh, po_lines pol, po_line_locations poll
7358: WHERE poh.po_header_id = p_header_id
7359: AND poh.po_header_id = pol.po_header_id
7360: AND pol.line_num = p_line_num
7361: AND pol.po_line_id = poll.po_line_id (+)
7391: 'QUOTATION', DECODE(poll.ship_to_location_id,
7392: NULL,poll.price_override, pol.unit_price),
7393: pol.unit_price)
7394: , DECODE(poll.line_location_id, NULL, pol.unit_meas_lookup_code, poll.unit_meas_lookup_code)
7395: FROM po_headers poh, po_lines pol, po_line_locations poll
7396: WHERE poh.po_header_id = p_header_id
7397: AND poh.po_header_id = pol.po_header_id
7398: AND pol.line_num = p_line_num
7399: AND pol.po_line_id = poll.po_line_id (+)
7823: FUNCTION get_last_update_date_for_doc(p_doc_header_id IN NUMBER) RETURN DATE
7824: IS
7825: header_last_update_date PO_HEADERS_ALL.last_update_date%type;
7826: line_last_update_date PO_LINES_ALL.last_update_date%type;
7827: line_loc_last_update_date PO_LINE_LOCATIONS_ALL.last_update_date%type;
7828: dist_last_update_date PO_DISTRIBUTIONS_ALL.last_update_date%type;
7829: doc_last_update_date PO_HEADERS_ALL.last_update_date%type;
7830: --Lowest date supported by database as we need to handle null dates
7831: min_date PO_HEADERS_ALL.last_update_date%type := to_date('01/01/-4712','DD/MM/SYYYY');
7842: 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;
7843: d_pos := 20;
7844: 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;
7845: d_pos := 30;
7846: 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;
7847: d_pos := 40;
7848: 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;
7849: d_pos := 50;
7850: doc_last_update_date := greatest( header_last_update_date