[Home] [Help]
280: -- po_header_id
281: --p_document_type
282: -- po document type PO or PA
283: --p_document_subtype
284: -- po_document subtype 'STANDARD', 'CONTRACT' or 'BLANKET'
285: --p_online_report_id
286: -- unique error report id
287: --p_user_id
288: -- user id
461: * Valid Document types and Document subtypes are
462: * Document Type Document Subtype
463: * REQUISITION --->
464: * RELEASE ---> SCHEDULED/BLANKET
465: * PO ---> PLANNED/STANDARD
466: * PA ---> CONTRACT/BLANKET
467: --
468: --
469: -- The following 2 parameters replace the p_document_id parameter.
578: PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_level_id',p_document_level_id);
579: PO_DEBUG.debug_var(l_log_head,l_progress,'p_check_asl',p_check_asl);
580: END IF;
581:
582: --Standard Start og API savepoint
583: SAVEPOINT PO_SUBMISSION_CHECK_SP;
584:
585: l_progress := '000';
586: -- Standard call to check for call compatibility
582: --Standard Start og API savepoint
583: SAVEPOINT PO_SUBMISSION_CHECK_SP;
584:
585: l_progress := '000';
586: -- Standard call to check for call compatibility
587: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
588: THEN
589: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
590: END IF;
929: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930: END IF;
931:
932: l_progress := '014';
933: --Do all checks common to Standard and PLanned POs
934: check_po(l_document_id,
935: l_online_report_id,
936: l_user_id,
937: l_login_id,
999: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1000: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1001: END IF;
1002:
1003: ELSIF p_document_subtype = 'STANDARD' THEN
1004:
1005: l_progress := '018';
1006: IF g_debug_stmt THEN
1007: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1005: l_progress := '018';
1006: IF g_debug_stmt THEN
1007: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
1008: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
1009: || l_progress,'PO: STANDARD checks');
1010: END IF;
1011: END IF;
1012: --Call additional checks for Standard PO
1013: check_standard_po(l_document_id,
1008: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
1009: || l_progress,'PO: STANDARD checks');
1010: END IF;
1011: END IF;
1012: --Call additional checks for Standard PO
1013: check_standard_po(l_document_id,
1014: l_online_report_id,
1015: l_user_id,
1016: l_login_id,
1009: || l_progress,'PO: STANDARD checks');
1010: END IF;
1011: END IF;
1012: --Call additional checks for Standard PO
1013: check_standard_po(l_document_id,
1014: l_online_report_id,
1015: l_user_id,
1016: l_login_id,
1017: p_sequence,
1154:
1155: --
1156:
1157: IF (p_document_type IN ('PO', 'PA')) AND
1158: (p_document_subtype IN ('STANDARD', 'BLANKET', 'CONTRACT'))
1159: AND (p_action_requested = g_action_DOC_SUBMISSION_CHECK) THEN
1160:
1161: -- SQL What: conterms exist flag
1162: -- SQL why : need to check before calling contracts qa
1458: SAVEPOINT POST_SUBMISSION_CHECK_SP;
1459:
1460: l_progress := '000';
1461:
1462: -- Standard call to check for call compatibility
1463: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1464: THEN
1465: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1466: END IF;
4086: AND POLL.po_header_id = p_document_id
4087: AND POL.line_type_id = PLT.line_type_id
4088: AND nvl(POLL.cancel_flag,'N') <> 'Y'
4089: AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
4090: AND POLL.shipment_type in ('PLANNED', 'STANDARD')
4091: GROUP BY POL.unit_meas_lookup_code, nvl(POLL.shipment_num,0),
4092: nvl(POL.line_num,0), nvl(POLL.quantity,0),
4093: nvl(POL.item_id,-1), POLL.price_override,
4094: nvl(POLL.line_location_id,0);
4096:
4097: --
4098: /* Setup the Complex Work PO select cursor
4099: ** Select Complex Work PO Line information and the
4100: ** line loc ID for the first STANDARD pay item for
4101: ** that line
4102: */
4103: CURSOR po_pay_item_cursor (p_document_id NUMBER) IS
4104: SELECT (POL.unit_price
4120: AND POLL.line_location_id =
4121: (SELECT min(POLL2.line_location_id)
4122: FROM PO_LINE_LOCATIONS_GT POLL2
4123: WHERE POLL2.po_line_id = POL.po_line_id
4124: AND POLL2.shipment_type = 'STANDARD'
4125: )
4126: GROUP BY POL.unit_price, POLL.quantity, nvl(POLL.quantity_cancelled, 0),
4127: POL.unit_meas_lookup_code,
4128: POLL.shipment_num,
5742: nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
5743: NOT EXISTS (SELECT 'Shipments Exist'
5744: FROM PO_LINE_LOCATIONS_GT PLL
5745: WHERE PLL.po_line_id = POL.po_line_id AND
5746: PLL.shipment_type in ('STANDARD','PLANNED'));
5747:
5748: --Increment the p_sequence with number of errors reported in last query
5749: p_sequence := p_sequence + SQL%ROWCOUNT;
5750: -------------------------------------------------
5799: PLL.po_header_id = p_document_id AND
5800: nvl(PLL.cancel_flag, 'N') = 'N' AND
5801: nvl(POL.cancel_flag, 'N') = 'N' AND
5802: nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
5803: PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
5805: FROM PO_DISTRIBUTIONS_GT POD
5806: WHERE POD.line_location_id = PLL.line_location_id);
5807:
5883: AND POH.po_header_id = p_document_id
5884: AND nvl(PLL.cancel_flag,'N') = 'N'
5885: AND nvl(POL.cancel_flag,'N') = 'N'
5886: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
5887: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
5888: AND SOB.set_of_books_id = FSP.set_of_books_id
5889: AND SOB.currency_code <> POH.currency_code
5890: AND (POD.rate is null
5891: OR (POH.rate_type <> 'User'
5942: AND POH.po_header_id = p_document_id
5943: AND nvl(PLL.cancel_flag,'N') = 'N'
5944: AND nvl(POL.cancel_flag,'N') = 'N'
5945: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
5946: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
5947: AND SOB.set_of_books_id = FSP.set_of_books_id
5948: AND SOB.currency_code = POH.currency_code
5949: AND POD.rate is not null;
5950:
5959: || l_progress,'PO 8: Contract Amount exceed check');
5960: END IF;
5961: END IF;
5962:
5963: -- Check 8: The amount of all standard purchase orders
5964: -- for a contract should not exceed the amount limit of the contract.
5965: -- PO_SUB_STD_CONTRACT_AMT_LIMIT
5966:
5967: -- bug3673292
6018: || l_progress,'PO 9: Ref to unapproved contract check ');
6019: END IF;
6020: END IF;
6021:
6022: -- Check 9: Any of the standard PO's lines should not references an
6023: -- unapproved contract.
6024: -- PO_SUB_REF_UNAPPROVED_CONTRACT
6025: --< Bug 3422733 > Only do this check if Contract is not ON HOLD. The ON HOLD
6026: -- check is done later. Avoids showing 2 msgs for Contract that is ON HOLD.
6078: || l_progress,'PO 10: Ref contract diff vendor');
6079: END IF;
6080: END IF;
6081:
6082: -- Check 10: Any of the standard PO's lines should not reference a
6083: -- contract whose vendor is different than the one on PO header.
6084: -- PO_SUB_LINE_CONTRACT_MISMATCH
6085:
6086: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_LINE_CONTRACT_MISMATCH');
6250: ** Split the ASL check to ASL check for Blanket and PO.
6251: ** Also modified the query for performance.
6252: ** UNION is replaced with UNION ALL in all ASL checks
6253: ** In PO_SUB_ITEM_NOT_APPROVED the Sum clause has also been modified
6254: ** PO_SUB_ITEM_NOT_APPROVED for Standard PO
6255: ** PA_SUB_ITEM_NOT_APPROVED for Blanket/Contract PO
6256: */
6257: /* Bug# 1570115: While checking for Restricted items the query was
6258: ** checking with FSP.inventory_organization_id to instead of checking with the
7039:
7040: -- item is not null
7041: AND POL.item_id IS NOT NULL
7042:
7043: -- Document is standard PO
7044: AND POH.type_lookup_code = 'STANDARD'
7045:
7046: --VMI is enabled
7047: AND PASL.item_id = POL.item_id
7040: -- item is not null
7041: AND POL.item_id IS NOT NULL
7042:
7043: -- Document is standard PO
7044: AND POH.type_lookup_code = 'STANDARD'
7045:
7046: --VMI is enabled
7047: AND PASL.item_id = POL.item_id
7048: AND PASL.vendor_id = POH.vendor_id
7987: END IF;
7988:
7989: END CHECK_BLANKET_AGREEMENT;
7990:
7991: --For Standard POs
7992: /**
7993: * Private Procedure: CHECK_STANDARD_PO
7994: * Requires:
7995: * IN PARAMETERS:
7989: END CHECK_BLANKET_AGREEMENT;
7990:
7991: --For Standard POs
7992: /**
7993: * Private Procedure: CHECK_STANDARD_PO
7994: * Requires:
7995: * IN PARAMETERS:
7996: * p_document_id: The requisition_header_id of submitted document
7997: * p_online_report_id: Id used to INSERT INTO online_report_text table
8000: * IN OUT PARAMETERS
8001: * p_sequence: Sequence number of last reported error
8002: * Modifies: Inserts error msgs in online_report_text_gt table, uses
8003: * global_temp tables for processing
8004: * Effects: This procedure runs the document submission checks for Standard
8005: * POs including GLOBAL AGREEMENTS reference checks and Consigned
8006: * Inventory checks
8007: * Returns:
8008: * p_sequence: This parameter contains the current count of number of error
8007: * Returns:
8008: * p_sequence: This parameter contains the current count of number of error
8009: * messages inserted
8010: */
8011: PROCEDURE check_standard_po(p_document_id IN NUMBER,
8012: p_online_report_id IN NUMBER,
8013: p_user_id IN NUMBER,
8014: p_login_id IN NUMBER,
8015: p_sequence IN OUT NOCOPY NUMBER,
8015: p_sequence IN OUT NOCOPY NUMBER,
8016: x_return_status OUT NOCOPY VARCHAR2) IS
8017:
8018: l_is_ga_referenced VARCHAR2(1) := NULL;
8019: l_api_name CONSTANT varchar2(40) := 'CHECK_STANDARD_PO';
8020: l_progress VARCHAR2(3);
8021:
8022: l_is_gc_referenced VARCHAR2(1) := NULL; --
8023: l_textline PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL; --< Shared Proc FPJ >
8047: BEGIN
8048:
8049: l_progress := '000';
8050:
8051: --check if atleast one line of Standard PO has Global Agreement reference
8052: --if so then call check_std_global_ref
8053: OPEN std_ga_ref_cursor(p_document_id);
8054:
8055: FETCH std_ga_ref_cursor INTO l_is_ga_referenced;
8144: l_api_name || '.others_exception', 'EXCEPTION: Location is '
8145: || l_progress || ' SQL CODE is '||sqlcode);
8146: END IF;
8147: END IF;
8148: END CHECK_STANDARD_PO;
8149:
8150:
8151: /**
8152: * Private Procedure: CHECK_STD_CONSIGNED_REF
8266: FROM po_lines_gt pol,
8267: po_line_locations_gt pll
8268: WHERE pol.po_header_id = p_document_id
8269: AND pol.po_line_id = pll.po_line_id
8270: AND pll.shipment_type = 'STANDARD'
8271: AND pll.consigned_flag = 'Y'
8272: AND nvl(pol.cancel_flag,'N') = 'N'
8273: AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
8274: AND (pol.oke_contract_header_id is not null or
8332: WHERE POH.po_header_id = p_document_id AND
8333: POH.po_header_id = POL.po_header_id AND
8334: -- POH.po_header_id = PLL.po_header_id AND
8335: POL.po_line_id = PLL.po_line_id AND
8336: PLL.shipment_type = 'STANDARD' AND
8337: nvl(PLL.cancel_flag,'N') = 'N' AND
8338: nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
8339: -- Bug fix for #2733398
8340: -- nvl(PLL.approved_flag, 'N') IN ('N','R') AND
8477:
8478: END CHECK_STD_CONSIGNED_REF;
8479:
8480:
8481: --For Standard POs
8482: /**
8483: * Private Procedure: CHECK_STD_GLOBAL_REF
8484: * Requires:
8485: * IN PARAMETERS:
8490: * IN OUT PARAMETERS
8491: * p_sequence: Sequence number of last reported error
8492: * Modifies: Inserts error msgs in online_report_text_gt table, uses
8493: * global_temp tables for processing
8494: * Effects: This procedure runs the document submission checks for Standard
8495: * POs which have GLOBAL AGREEMENTS reference
8496: * Returns:
8497: * p_sequence: This parameter contains the current count of number of error
8498: * messages inserted
8597: || l_progress,'PO STD GA 2: Ref GA approved');
8598: END IF;
8599: END IF;
8600:
8601: --Check 2: If the standard PO line is referencing a GA then that GA
8602: --should be in an approved status.
8603: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8604: --< Bug 3422733 > Only do this check if GA is not ON HOLD. The ON HOLD check
8605: -- is done later. Avoids showing 2 msgs for Contract that is ON HOLD.
9018: || l_progress,'PO STD GA 9: Amt limit check for GA header');
9019: END IF;
9020: END IF;
9021:
9022: --Check 9: The total amount on all the standard PO SHIPMENT lines in the
9023: --current setup referencing the same GA should be less than the
9024: --amount limit on the GA header
9025:
9026: --
9064:
9065: -- bug3413891
9066: -- 1) Added optimizer hint FULL(POL)
9067: -- 2) Removed the join to PO_LINE_LOCATIONS_GT as it will bring in wrong
9068: -- result if the standard PO line has multiple shipments
9069:
9070: SELECT /*+ FULL(POL) */
9071: POL.line_num
9072: , nvl ( decode ( POL.quantity --
9174: --Check 10: If price override is yes then the line unit price cannot be more
9175: --than the price tolerance on the GA line.
9176: --
9177: -- Bug 3177525: This check should not be done if allow_price_override_flag
9178: -- is 'N' because the user can never modify the standard PO price/amount;
9179: -- it is automatically defaulted.
9180:
9181: INSERT into po_online_report_text_gt(online_report_id,
9182: last_update_login,
9261: || l_progress,'PO STD GA 11: Min Release Amount at line level check');
9262: END IF;
9263: END IF;
9264:
9265: --Check 11: The total amount on all the lines on the standard PO
9266: --referencing same GA line should be greater than the minimum release amount
9267: --on the GA line.
9268: l_textline := -- <2710030>
9269: FND_MESSAGE.GET_STRING('PO','PO_SUB_STD_GA_LINE_LESS_MINREL');
9796: || l_progress,'PA CONTRACT 1: Released Amount exceeds Amount Limit');
9797: END IF;
9798: END IF;
9799:
9800: -- Check 1: The amount of all standard purchase orders
9801: -- for a contract should not exceed the amount limit of the contract.
9802: -- PO_SUB_CONTRACT_AMT_LIMIT
9803: --
9804: --instead of summing up the Line Loc values. The results should be
10091: --p_doc_level_id
10092: -- Id of the doc level type of which to populate the tables.
10093: --OUT:
10094: --x_return_status
10095: -- APPS standard parameter.
10096: --Testing:
10097: --
10098: --End of Comments
10099: -------------------------------------------------------------------------------
10378: amount)
10379: --
10380: WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i);
10381:
10382: --To propogate line price change to shipment level for Standard PO
10383: IF (p_document_type = 'PO' AND p_document_subtype = 'STANDARD') THEN
10384:
10385: l_progress := '002';
10386: -- SQL What: Setting the priceoverride at Shipment level
10379: --
10380: WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i);
10381:
10382: --To propogate line price change to shipment level for Standard PO
10383: IF (p_document_type = 'PO' AND p_document_subtype = 'STANDARD') THEN
10384:
10385: l_progress := '002';
10386: -- SQL What: Setting the priceoverride at Shipment level
10387: -- SQL Why: Need to propogate line price change to shipment level
10384:
10385: l_progress := '002';
10386: -- SQL What: Setting the priceoverride at Shipment level
10387: -- SQL Why: Need to propogate line price change to shipment level
10388: -- for Standard PO
10389: -- SQL Join: po_line_id
10390: FORALL i IN 1..p_requested_changes.line_changes.po_line_id.COUNT
10391: UPDATE po_line_locations_gt
10392: SET price_override = nvl(p_requested_changes.line_changes.unit_price(i),price_override)
12468:
12469: BEGIN
12470:
12471: l_progress := '000';
12472: -- Standard call to check for call compatibility
12473: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
12474: THEN
12475: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
12476: END IF;
12541: p_employee_id || '=' || p_owner_id_column ||')';
12542: /* OR (' ||
12543: 'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
12544: 'WHERE POAH2.employee_id = ' || p_employee_id ||
12545: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
12546: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || '))))';*/
12547:
12548: x_where_clause := x_where_clause ||
12549: ' OR '||'('||p_query_table || '.security_level_code = ''HIERARCHY'' AND '||
12550: '((' || p_employee_id || '=' || p_owner_id_column ||') OR (' ||
12551: 'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
12552: 'WHERE POAH2.employee_id =' || p_employee_id ||
12553: /* Bug 7229262/7239696
12554: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
12555: */
12556: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
12557: /* End Bug 7229262/7239696 */
12558:
12552: 'WHERE POAH2.employee_id =' || p_employee_id ||
12553: /* Bug 7229262/7239696
12554: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
12555: */
12556: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
12557: /* End Bug 7229262/7239696 */
12558:
12559: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
12560: p_employee_id || ' IN (SELECT H.superior_id ' ||
12569: '((' || p_employee_id || '=' || p_owner_id_column ||') OR (' ||
12570: 'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
12571: 'WHERE POAH2.employee_id =' || p_employee_id ||
12572: /* Bug 7229262/7239696
12573: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
12574: */
12575: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
12576: /* End Bug 7229262/7239696 */
12577: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
12571: 'WHERE POAH2.employee_id =' || p_employee_id ||
12572: /* Bug 7229262/7239696
12573: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
12574: */
12575: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
12576: /* End Bug 7229262/7239696 */
12577: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
12578: 'EXISTS(SELECT NULL FROM PO_AGENTS WHERE agent_id= ' ||
12579: p_employee_id || ' AND SYSDATE BETWEEN NVL(start_date_active, ' ||
13481: || l_progress, 'Entering Procedure '||l_api_name);
13482: END IF;
13483: END IF;
13484:
13485: -- Standard call to check for call compatibility
13486: l_progress := '010';
13487: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
13488: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
13489: END IF;
15338: BEGIN
15339:
15340: d_progress := 10;
15341:
15342: IF (((p_document_type = g_document_type_PO) AND (p_document_subtype = 'STANDARD'))
15343: OR (p_document_type = g_document_type_RELEASE))
15344: THEN
15345:
15346: d_progress := 20;
16345: FROM po_lines_gt pol, po_line_locations_gt poll
16346: , po_distributions_gt pod, gl_code_combinations gcc
16347: WHERE pod.line_location_id = poll.line_location_id
16348: AND pol.po_line_id = poll.po_line_id
16349: AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'PREPAYMENT') --
16351: AND ( NVL(poll.cancel_flag, 'N') = 'N' OR p_action_requested = g_action_FINAL_CLOSE_CHECK)
16352: AND gcc.code_combination_id = pod.code_combination_id
16353: AND NVL(p_action_date, trunc(SYSDATE)) NOT BETWEEN
16850: PO_LINE_LOCATIONS_GT PLL
16851: , PO_LINES_GT POL
16852: WHERE
16853: POL.po_line_id = PLL.po_line_id
16854: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
16855: AND POL.po_header_id = p_document_id
16856: AND nvl(POL.cancel_flag,'N') = 'N'
16857: AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
16858: AND (
16861: (SELECT round(sum(PLL2.quantity) -
16862: sum(nvl(PLL2.quantity_cancelled, 0)), 10)
16863: FROM PO_LINE_LOCATIONS_GT PLL2
16864: WHERE PLL2.po_line_id = POL.po_line_id AND
16865: PLL2.shipment_type in ('STANDARD', 'PLANNED') ) )
16866: )
16867: OR --
16868: ( ( POL.amount IS NOT NULL )
16869: AND ( round(POL.amount, 10) <>
16872: , 10
16873: )
16874: FROM po_line_locations_gt PLL3
16875: WHERE PLL3.po_line_id = POL.po_line_id
16876: AND PLL3.shipment_type IN ('STANDARD','PLANNED')
16877: )
16878: )
16879: )
16880: )
16969: l_progress := '023';
16970:
16971: --Bug 5440038 , included ADVANCES for roll-up logic but this would apply
16972: -- only to financing case, as the for actual case only the
16973: -- STANDARD line locations are rolled up
16974: -- for a financing case, the line location rollups would include all pay items
16975: -- and the advance amounts which together make up the prepayment amount
16976: -- should be less than the line amount
16977: -- the financing_advance_amount advance amount is also calculated for
17032: PO_LINE_LOCATIONS_GT PLL
17033: , PO_LINES_GT POL
17034: WHERE
17035: POL.po_line_id = PLL.po_line_id
17036: AND ( (l_is_financing_flag = 'N' AND PLL.shipment_type = 'STANDARD')
17037: OR (l_is_financing_flag = 'Y' and PLL.shipment_type = 'PREPAYMENT'))
17038: AND POL.po_header_id = p_document_id
17039: AND nvl(POL.cancel_flag,'N') = 'N'
17040: AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
17137: AND POD.line_location_id = PLL.line_location_id
17138: AND PLL.po_header_id = p_document_id
17139: AND nvl(PLL.cancel_flag,'N') = 'N'
17140: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
17141: AND PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
17143: POL.line_num
17144: , PLL.shipment_num
17145: , PLL.quantity
17410: x_invoice_type => l_invoice_type ,
17411: x_calling_sequence => l_calling_sequence,
17412: x_return_status => l_return_status);
17413:
17414: IF p_document_type = 'PO' AND p_document_subtype = 'STANDARD'
17415: AND (p_document_level = g_document_level_HEADER
17416: OR p_document_level = g_document_level_LINE) THEN
17417: --
17418: -- Handle Header final close / Unreserve SPO
17645: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
17646: PO_LOG.proc_begin(d_module, 'p_doc_level_id', p_doc_level_id);
17647: END IF;
17648:
17649: IF p_document_subtype = 'STANDARD' THEN
17650: IF p_document_level = g_document_level_HEADER THEN
17651: l_document_id := p_doc_level_id;
17652: ELSIF p_document_level = g_document_level_LINE THEN
17653: SELECT po_header_id