[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
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:
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:
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
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