[Home] [Help]
906: -- SYSDATE.
907: --Notes:
908: -- This procedure was refactored in FPJ to call the more generalized
909: -- procedure get_period_info. However, the parameter names were
910: -- not changed to meet standards, as that may have impacted calling code.
911: --Testing:
912: --
913: --End of Comments
914: -------------------------------------------------------------------------------
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:
1271: else
1272: x_progress := 42;
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:
1299: end if;
1300:
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:
1329: else
1330: x_progress := 46;
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:
1356:
1357: end if;
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
1410: /* Bug 1111926: GMudgal 2/18/2000
1411: ** Incorrect placement of brackets caused incorrect rounding
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;
1436:
1437: end if;
1846: PO_HEADERS_ALL PH
1847: WHERE PH.po_header_id = x_object_id
1848: AND C.currency_code = PH.CURRENCY_CODE;
1849:
1850: /* 716188 - SVAIDYAN : Changed the sql stmt to select only Standard and Planned
1851: POs that reference this contract and also to convert the amount into the
1852: Contract's currency. This is achieved by converting the PO amt first to the
1853: functional currency and then changing this to the Contract currency */
1854:
1914: PO_HEADERS_ALL PH1
1915: WHERE PH.po_header_id = x_object_id
1916: AND PH.po_header_id = PL.contract_id
1917: AND PL.po_line_id = PLL.po_line_id
1918: AND PLL.shipment_type IN ('STANDARD','PLANNED')
1919: AND POD.line_location_id(+) = PLL.line_location_id
1920: AND PH1.po_header_id = PL.po_header_id;*/
1921:
1922: --Get release amount for documents with same currency
1931: PO_HEADERS_ALL PH1
1932: WHERE PH.po_header_id = x_object_id
1933: AND PH.po_header_id = PL.contract_id
1934: AND PL.po_line_id = PLL.po_line_id
1935: AND PLL.shipment_type IN ('STANDARD','PLANNED')
1936: AND PH1.po_header_id = PL.po_header_id
1937: and PH.currency_code = PH1.currency_code;
1938:
1939: --Get release amount for documents with different currency
1951: PO_HEADERS_ALL PH1
1952: WHERE PH.po_header_id = x_object_id
1953: AND PH.po_header_id = PL.contract_id
1954: AND PL.po_line_id = PLL.po_line_id
1955: AND PLL.shipment_type IN ('STANDARD','PLANNED')
1956: AND POD.line_location_id(+) = PLL.line_location_id
1957: AND PH1.po_header_id = PL.po_header_id
1958: and PH.currency_code <> PH1.currency_code;
1959:
2001: WHERE PH.po_header_id = x_object_id
2002: AND PH.po_header_id = PL.contract_id --
2003: --AND PH.currency_code = C.currency_code
2004: AND PL.po_line_id = PLL.po_line_id
2005: AND PLL.shipment_type in ('STANDARD','PLANNED')
2006: AND POD.line_location_id(+) = PLL.line_location_id
2007: AND PH1.po_header_id = PL.po_header_id;*/
2008:
2009: --Get release amount for documents with same currency
2018: PO_HEADERS_ALL PH1
2019: WHERE PH.po_header_id = x_object_id
2020: AND PH.po_header_id = PL.contract_id
2021: AND PL.po_line_id = PLL.po_line_id
2022: AND PLL.shipment_type IN ('STANDARD','PLANNED')
2023: AND PH1.po_header_id = PL.po_header_id
2024: and PH.currency_code = PH1.currency_code;
2025:
2026: --Get release amount for documents with different currency
2038: PO_HEADERS_ALL PH1
2039: WHERE PH.po_header_id = x_object_id
2040: AND PH.po_header_id = PL.contract_id
2041: AND PL.po_line_id = PLL.po_line_id
2042: AND PLL.shipment_type IN ('STANDARD','PLANNED')
2043: AND POD.line_location_id(+) = PLL.line_location_id
2044: AND PH1.po_header_id = PL.po_header_id
2045: and PH.currency_code <> PH1.currency_code;
2046:
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
2301: x_progress := 234;
2302:
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;
2324:
2325: elsif (x_object_type = 'S' ) then /* PO Shipment */
2407: -- None.
2408: --Locks:
2409: -- None.
2410: --Function:
2411: -- Returns total amount for latest archived revision for Standard Purchase order
2412: --Parameters:
2413: --IN:
2414: -- p_object_id
2415: -- PO header id
2415: -- PO header id
2416: -- p_doc_type
2417: -- The main doc type for PO. Valid values are 'PO'
2418: -- p_doc_subtype
2419: -- The lookup code of the document. Valid values are 'STANDARD'
2420: -- p_base_cur_result
2421: -- Whether result should be returned in base currency or transaction currency
2422: -- Valid Values are
2423: -- 'Y'- Return result in Base/Functional Currency for the org
2441: l_archive_total_amt NUMBER;
2442: l_progress VARCHAR2(3):='000';
2443: BEGIN
2444: l_progress := '010';
2445: IF (p_doc_type = 'PO') AND (p_doc_subtype = 'STANDARD') then
2446:
2447: --Get the currency code of the PO and the base currency code
2448:
2449: po_core_s2.get_po_currency (x_object_id=>p_object_id,
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'
2507: AND POD.LATEST_EXTERNAL_FLAG = 'Y';
2508:
2506: AND PLL.LATEST_EXTERNAL_FLAG = 'Y'
2507: AND POD.LATEST_EXTERNAL_FLAG = 'Y';
2508:
2509: l_progress := '060';
2510: END if;-- (p_doc_type = 'PO') AND (p_doc_subtype = 'STANDARD')
2511: l_progress := '070';
2512: --If l_archive_total_amt has a null value, return 0 as the total.
2513: IF l_archive_total_amt IS NULL THEN
2514: l_archive_total_amt := 0;
2549: l_progress VARCHAR2(3):='000';
2550:
2551: BEGIN
2552: l_progress := '010';
2553: IF (p_doc_type = 'PO') AND (p_doc_subtype = 'STANDARD') then
2554:
2555: IF p_object_type = 'H' then
2556:
2557: --Get the currency code of the PO and the base currency code
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
2617: WHERE pll1.line_location_id = pll.line_location_id AND
2618: pll1.revision_num <= p_doc_revision)
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
2723: pll1.revision_num <= p_doc_revision) ;
2724:
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
2746: pll1.revision_num <= p_doc_revision) ;
2747:
2978: end if;
2979:
2980:
2981: end if;
2982: END IF;-- (p_doc_type = 'PO') AND (p_doc_subtype = 'STANDARD')
2983:
2984:
2985: l_progress := '250';
2986: --If l_archive_total_amt has a null value, return 0 as the total.
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:
3058: else
3059: --
3060: -- For the new Services lines, quantity will be null.
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:
3078: end if;
3079: return(x_result_fld);
3080: END GET_RELEASE_LINE_TOTAL;
3083:
3084: FUNCTION: get_ga_amount_released
3085:
3086: DESCRIPTION: Gets the total Amount Released for a particular Global Agreement.
3087: That is, sum up the total for all uncancelled Standard PO lines
3088: which reference that Global Agreement.
3089:
3090: ===================================================================================*/
3091: FUNCTION get_ga_amount_released
3129: l_po_precision, --OUT
3130: l_po_ext_precision, --OUT
3131: l_po_min_unit ); --OUT
3132:
3133: -- NOTE: All Standard PO's must have the same currency code as the
3134: -- Global Agreement that they reference. The currency conversion rate used
3135: -- will be the rate between the Global Agreement's currency and the owning
3136: -- org's currency.
3137:
3316:
3317: FUNCTION: get_ga_line_amount_released
3318:
3319: DESCRIPTION: Gets the total Amount Released for a Global Agreement line.
3320: That is, sum up the total for all uncancelled Standard PO lines
3321: which reference that Global Agreement line.
3322:
3323: ===================================================================================*/
3324: PROCEDURE get_ga_line_amount_released
3462: x_min_unit => l_po_min_unit );
3463:
3464: --Bug# 5391346 START:
3465: --Added the following to queries below as was coded in the local contract case (refer bug# 716188)
3466: --1. Where clause POLL.shipment_type in ('STANDARD','PLANNED')
3467: --2. Outer Join POD.line_location_id(+) = POLL.line_location_id instead of simple join
3468: --3. Proper rate conversions taking into account different currencies of GCPA and SPOs sourced to it.
3469:
3470:
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
3549: AND POLL.shipment_type in ('STANDARD','PLANNED')
3550: AND POD.line_location_id = POLL.line_location_id --bug 12572504
3551: AND PH1.po_header_id = POL.po_header_id
3552: AND POD.po_line_id = POLL.po_line_id
3553: AND Nvl(poll.consigned_flag,'N') <> 'Y';
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
3617: AND POLL.shipment_type in ('STANDARD','PLANNED')
3618: AND POD.line_location_id = POLL.line_location_id --bug 12572504
3619: AND PH1.po_header_id = POL.po_header_id
3620: AND POD.po_line_id = POLL.po_line_id
3621: AND Nvl(poll.consigned_flag,'N') <> 'Y';
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
3686: AND POLL.shipment_type in ('STANDARD','PLANNED')
3687: AND POD.line_location_id = POLL.line_location_id --bug 12572504
3688: AND PH1.po_header_id = POL.po_header_id
3689: AND POD.po_line_id = POLL.po_line_id
3690: AND Nvl(poll.consigned_flag,'N') <> 'Y';
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
3751: AND POLL.shipment_type in ('STANDARD','PLANNED')
3752: AND POD.line_location_id = POLL.line_location_id --bug 12572504
3753: AND PH1.po_header_id = POL.po_header_id
3754: AND POD.po_line_id = POLL.po_line_id
3755: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3820: PO_HEADERS_ALL PH1
3821: WHERE PH.po_header_id = p_po_header_id
3822: AND PH.po_header_id = POL.contract_id
3823: AND POL.po_line_id = POLL.po_line_id
3824: AND POLL.shipment_type in ('STANDARD','PLANNED')
3825: AND POD.line_location_id = POLL.line_location_id --bug 12572504
3826: AND PH1.po_header_id = POL.po_header_id
3827: AND POD.po_line_id = POLL.po_line_id
3828: AND Nvl(poll.consigned_flag,'N') <> 'Y';
3889: PO_HEADERS_ALL PH1
3890: WHERE PH.po_header_id = p_po_header_id
3891: AND PH.po_header_id = POL.contract_id --
3892: AND POL.po_line_id = POLL.po_line_id
3893: AND POLL.shipment_type in ('STANDARD','PLANNED')
3894: AND POD.line_location_id = POLL.line_location_id --bug 12572504
3895: AND PH1.po_header_id = POL.po_header_id
3896: AND POD.po_line_id = POLL.po_line_id
3897: AND Nvl(poll.consigned_flag,'N') <> 'Y';
5869: -- The doc number whose uniqueness needs to be tested
5870: -- p_org_id
5871: -- The operating unit where the uniqueness needs to be tested
5872: -- p_type_lookup_code
5873: -- The lookup code of the document. Valid values are 'STANDARD', 'PLANNED',
5874: -- 'CONTRACT','BLANKET', 'RFQ', 'QUOTATION'
5875: --Testing:
5876: -- None
5877: --End of Comments
5901: (SELECT 'po number is not unique'
5902: FROM po_headers_all ph
5903: WHERE ph.segment1 = p_segment1
5904: AND ph.type_lookup_code IN
5905: ('STANDARD','CONTRACT','BLANKET','PLANNED')
5906: AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
5907:
5908: l_Progress := '020';
5909:
5914: (SELECT 'po number is not unique'
5915: FROM po_history_pos_all ph
5916: WHERE ph.segment1 = p_segment1
5917: AND ph.type_lookup_code IN
5918: ('STANDARD','CONTRACT','BLANKET','PLANNED')
5919: AND nvl(ph.org_id, -99) = nvl(p_org_id, -99));
5920:
5921: --Get the install status of Sourcing
5922: po_setup_s1.get_sourcing_startup(l_pon_install_status);
5921: --Get the install status of Sourcing
5922: po_setup_s1.get_sourcing_startup(l_pon_install_status);
5923:
5924: if nvl(l_pon_install_status,'N') ='I' then
5925: if p_Type_lookup_code in ('STANDARD','BLANKET') then
5926: pon_auction_po_pkg.check_unique(p_org_id,p_segment1,l_status);
5927: if l_status = 'SUCCESS' then
5928: l_Unique :=TRUE;
5929: else
8400: PO_LOG.proc_begin(d_module_base); PO_LOG.proc_begin(d_module_base, 'p_doc_sub_type', p_doc_sub_type);
8401: END IF;
8402: d_pos := 10;
8403: -- Derive Document Type
8404: IF (p_doc_sub_type = 'STANDARD') THEN
8405: l_doc_type := g_doc_type_PO;
8406: ELSIF (p_doc_sub_type IN ('BLANKET', 'CONTRACT')) THEN
8407: l_doc_type := g_doc_type_PA;
8408: ELSE