[Home] [Help]
361: -- po_header_id
362: --p_document_type
363: -- po document type PO or PA
364: --p_document_subtype
365: -- po_document subtype 'STANDARD', 'CONTRACT' or 'BLANKET'
366: --p_online_report_id
367: -- unique error report id
368: --p_user_id
369: -- user id
1079: * Valid Document types and Document subtypes are
1080: * Document Type Document Subtype
1081: * REQUISITION --->
1082: * RELEASE ---> SCHEDULED/BLANKET
1083: * PO ---> PLANNED/STANDARD
1084: * PA ---> CONTRACT/BLANKET
1085: --
1086: --
1087: -- The following 2 parameters replace the p_document_id parameter.
1239: PO_DEBUG.debug_var(l_log_head,l_progress,'p_document_level_id',p_document_level_id);
1240: PO_DEBUG.debug_var(l_log_head,l_progress,'p_check_asl',p_check_asl);
1241: END IF;
1242:
1243: --Standard Start og API savepoint
1244: SAVEPOINT PO_SUBMISSION_CHECK_SP;
1245:
1246: l_progress := '000';
1247: -- Standard call to check for call compatibility
1243: --Standard Start og API savepoint
1244: SAVEPOINT PO_SUBMISSION_CHECK_SP;
1245:
1246: l_progress := '000';
1247: -- Standard call to check for call compatibility
1248: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1249: THEN
1250: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1251: END IF;
1419: END IF;
1420: -- SGD PROJECT VALIDATIONS AT THE TIME OF SUBMISSION END
1421:
1422: IF (p_document_type IN ('PO', 'PA'))
1423: AND (p_document_subtype IN ('STANDARD', 'BLANKET', 'CONTRACT'))
1424: THEN
1425: -- CONTRACTING OFFICER VALIDATION
1426: --IF l_is_clm_doc= 'Y' THEN
1427: IF (PO_CLM_INTG_GRP.is_clm_document(p_doc_type => p_document_type,
1479: END IF; -- l_clm_document = 'Y' THEN
1480: END IF; --IF (p_document_type IN ('PO', 'PA'))
1481: -- BELOW CODE FOR CLM PROJECT
1482: IF (p_document_type IN ('PO', 'PA'))
1483: AND (p_document_subtype IN ('STANDARD', 'BLANKET', 'CONTRACT'))
1484: AND (p_action_requested = g_action_DOC_SUBMISSION_CHECK)
1485: THEN
1486:
1487: l_progress := '680';
1502:
1503: l_progress := '690';
1504: PO_DEBUG.debug_stmt(l_log_head || l_api_name, l_progress, 'Starting effective check for print form formats');
1505:
1506: -- Standard form and document format should not be null and
1507: -- not expired on the current date.
1508: IF p_draft_id <> -1 THEN
1509: l_print_doc_type := 'PO_MOD_STD_FORM';
1510: else
1522:
1523: if l_is_ord_off_idv = 'Y' then
1524: l_print_doc_type := 'PO_DEL_ORD_STD_FORM';
1525: else
1526: IF p_document_subtype = 'STANDARD' THEN
1527: l_print_doc_type := 'PO_AWARD_STD_FORM';
1528: ELSIF p_document_subtype = 'BLANKET' THEN
1529: l_print_doc_type := 'PO_IDV_STD_FORM';
1530: ELSIF p_document_subtype = 'CONTRACT' THEN
1571: AND NOT EXISTS (
1572: SELECT 1
1573: FROM po_print_form_formats ppff
1574: WHERE ppff.document_type = l_print_doc_type
1575: AND ppff.standard_form = phmv.clm_standard_form
1576: AND ppff.document_format = phmv.clm_document_format
1577: AND trunc(sysdate) <= trunc(nvl(ppff.inactive_date, sysdate +1))
1578: )
1579: AND Nvl(phmv.draft_type, '*') <> 'PAR'; --
1614: AND NOT EXISTS (
1615: SELECT 1
1616: FROM po_print_form_formats ppff
1617: WHERE ppff.document_type = l_print_doc_type
1618: AND ppff.standard_form = phmv.clm_standard_form
1619: AND ppff.document_format = phmv.clm_document_format
1620: AND trunc(sysdate) <= trunc(nvl(ppff.inactive_date, sysdate +1))
1621: );
1622: END IF;
2396: END IF;
2397: --Added for bug 12951645
2398:
2399: l_progress := '014';
2400: --Do all checks common to Standard and PLanned POs
2401: check_po(l_document_id,
2402: l_online_report_id,
2403: l_user_id,
2404: l_login_id,
2499: IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2500: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2501: END IF;
2502:
2503: ELSIF p_document_subtype = 'STANDARD' THEN
2504:
2505: l_progress := '018';
2506: IF g_debug_stmt THEN
2507: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2505: l_progress := '018';
2506: IF g_debug_stmt THEN
2507: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2508: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2509: || l_progress,'PO: STANDARD checks');
2510: END IF;
2511: END IF;
2512: --Call additional checks for Standard PO
2513: check_standard_po(l_document_id,
2508: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2509: || l_progress,'PO: STANDARD checks');
2510: END IF;
2511: END IF;
2512: --Call additional checks for Standard PO
2513: check_standard_po(l_document_id,
2514: l_online_report_id,
2515: l_user_id,
2516: l_login_id,
2509: || l_progress,'PO: STANDARD checks');
2510: END IF;
2511: END IF;
2512: --Call additional checks for Standard PO
2513: check_standard_po(l_document_id,
2514: l_online_report_id,
2515: l_user_id,
2516: l_login_id,
2517: p_sequence,
2524: l_progress := '019';
2525: IF g_debug_stmt THEN
2526: IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
2527: FND_LOG.string(FND_LOG.LEVEL_STATEMENT,g_log_head || '.'||l_api_name||'.'
2528: || l_progress,'PO: STANDARD checks');
2529: END IF;
2530: END IF;
2531:
2532: IF l_is_clm_doc = 'Y' THEN
2842: --bug 16713804
2843:
2844: /* CLM Phase 4 - Elins project Start */
2845: IF (p_document_type IN ('PO', 'PA')) AND
2846: (p_document_subtype IN ('STANDARD', 'BLANKET'))
2847: AND (p_action_requested = g_action_DOC_SUBMISSION_CHECK) THEN
2848:
2849: -- Profile Option IsExhibitEnabled
2850: l_isexhibitenabled := Nvl(po_core_s.Retrieveoptionvalue(l_org_id,
2886:
2887: --
2888:
2889: IF (p_document_type IN ('PO', 'PA')) AND
2890: (p_document_subtype IN ('STANDARD', 'BLANKET', 'CONTRACT'))
2891: AND (p_action_requested = g_action_DOC_SUBMISSION_CHECK) THEN
2892:
2893: -- SQL What: conterms exist flag
2894: -- SQL why : need to check before calling contracts qa
3244: SAVEPOINT POST_SUBMISSION_CHECK_SP;
3245:
3246: l_progress := '000';
3247:
3248: -- Standard call to check for call compatibility
3249: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
3250: THEN
3251: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3252: END IF;
6009: AND POLL.po_header_id = p_document_id
6010: AND POL.line_type_id = PLT.line_type_id
6011: AND nvl(POLL.cancel_flag,'N') <> 'Y'
6012: AND nvl(POLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
6013: AND POLL.shipment_type in ('PLANNED', 'STANDARD')
6014: GROUP BY POL.unit_meas_lookup_code, nvl(POLL.shipment_num,0),
6015: nvl(POL.line_num,0), nvl(POLL.quantity,0),
6016: nvl(POL.item_id,-1), POLL.price_override,
6017: nvl(POLL.line_location_id,0);
6019:
6020: --
6021: /* Setup the Complex Work PO select cursor
6022: ** Select Complex Work PO Line information and the
6023: ** line loc ID for the first STANDARD pay item for
6024: ** that line
6025: */
6026: CURSOR po_pay_item_cursor (p_document_id NUMBER) IS
6027: SELECT (POL.unit_price
6044: AND POLL.line_location_id =
6045: (SELECT min(POLL2.line_location_id)
6046: FROM PO_LINE_LOCATIONS_GT POLL2
6047: WHERE POLL2.po_line_id = POL.po_line_id
6048: AND POLL2.shipment_type = 'STANDARD'
6049: )
6050: GROUP BY POL.unit_price, POLL.quantity, nvl(POLL.quantity_cancelled, 0),
6051: POL.unit_meas_lookup_code,
6052: POLL.shipment_num,
8019: substr(l_textline,1,240),
8020: 'PO_SUB_AWARD_TYPE_NULL'
8021: FROM PO_HEADERS_GT POH
8022: WHERE POH.po_header_id = p_document_id
8023: AND POH.type_lookup_code = 'STANDARD'
8024: AND POH.clm_award_type IS NULL
8025: AND nvl(l_clm_po,'N') = 'Y';
8026:
8027: --Increment the p_sequence with number of errors reported in last query
8057: substr(l_textline,1,240),
8058: 'PO_SUB_IDV_WITHOUT_SOURCE'
8059: FROM PO_HEADERS_GT POH
8060: WHERE POH.po_header_id = p_document_id
8061: AND POH.type_lookup_code = 'STANDARD'
8062: AND Nvl(POH.clm_award_type,'N') IN (SELECT lookup_code
8063: FROM PO_LOOKUP_CODES
8064: WHERE lookup_type = 'PO_CLM_AWD_TYPE_ORD_OFF_IDV')
8065: AND POH.clm_source_document_id IS NULL
8098: substr(l_textline,1,240),
8099: 'PO_SUB_AWARD_WITH_SOURCE'
8100: FROM PO_HEADERS_GT POH
8101: WHERE POH.po_header_id = p_document_id
8102: AND POH.type_lookup_code = 'STANDARD'
8103: AND Nvl(POH.clm_award_type,'N') IN (SELECT lookup_code
8104: FROM PO_LOOKUP_CODES
8105: WHERE lookup_type = 'PO_CLM_AWD_TYPE_SPO')
8106: AND (POH.clm_source_document_id IS NOT NULL
8374: nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
8375: NOT EXISTS (SELECT 'Shipments Exist'
8376: FROM PO_LINE_LOCATIONS_GT PLL
8377: WHERE PLL.po_line_id = POL.po_line_id AND
8378: PLL.shipment_type in ('STANDARD','PLANNED'));
8379:
8380: --Increment the p_sequence with number of errors reported in last query
8381: p_sequence := p_sequence + SQL%ROWCOUNT;
8382: -------------------------------------------------
8440: PLL.po_header_id = p_document_id AND
8441: nvl(PLL.cancel_flag, 'N') = 'N' AND
8442: nvl(POL.cancel_flag, 'N') = 'N' AND
8443: nvl(PLL.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
8444: PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
8446: AND Nvl(POL.CLM_INFO_FLAG,'N')= 'N'
8447: AND NOT EXISTS (SELECT 'Distribution Exists'
8448: FROM PO_DISTRIBUTIONS_GT POD
8527: AND POH.po_header_id = p_document_id
8528: AND nvl(PLL.cancel_flag,'N') = 'N'
8529: AND nvl(POL.cancel_flag,'N') = 'N'
8530: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
8531: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
8532: AND SOB.set_of_books_id = FSP.set_of_books_id
8533: AND SOB.currency_code <> POH.currency_code
8534: AND (POD.rate is null
8535: OR (POH.rate_type <> 'User'
8586: AND POH.po_header_id = p_document_id
8587: AND nvl(PLL.cancel_flag,'N') = 'N'
8588: AND nvl(POL.cancel_flag,'N') = 'N'
8589: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
8590: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
8591: AND SOB.set_of_books_id = FSP.set_of_books_id
8592: AND SOB.currency_code = POH.currency_code
8593: AND POD.rate is not null;
8594:
8603: || l_progress,'PO 8: Contract Amount exceed check');
8604: END IF;
8605: END IF;
8606:
8607: -- Check 8: The amount of all standard purchase orders
8608: -- for a contract should not exceed the amount limit of the contract.
8609: -- PO_SUB_STD_CONTRACT_AMT_LIMIT
8610:
8611: -- bug3673292
8662: || l_progress,'PO 9: Ref to unapproved contract check ');
8663: END IF;
8664: END IF;
8665:
8666: -- Check 9: Any of the standard PO's lines should not references an
8667: -- unapproved contract.
8668: -- PO_SUB_REF_UNAPPROVED_CONTRACT
8669: --< Bug 3422733 > Only do this check if Contract is not ON HOLD. The ON HOLD
8670: -- check is done later. Avoids showing 2 msgs for Contract that is ON HOLD.
8722: || l_progress,'PO 10: Ref contract diff vendor');
8723: END IF;
8724: END IF;
8725:
8726: -- Check 10: Any of the standard PO's lines should not reference a
8727: -- contract whose vendor is different than the one on PO header.
8728: -- PO_SUB_LINE_CONTRACT_MISMATCH
8729: --< CLM Novation > bug13059027 - This check is a Warning for OrderOfIDV Modifications
8730: -- or Re-approval Base docs whose Supplier is different from that of referenced IDV
8897: ** Split the ASL check to ASL check for Blanket and PO.
8898: ** Also modified the query for performance.
8899: ** UNION is replaced with UNION ALL in all ASL checks
8900: ** In PO_SUB_ITEM_NOT_APPROVED the Sum clause has also been modified
8901: ** PO_SUB_ITEM_NOT_APPROVED for Standard PO
8902: ** PA_SUB_ITEM_NOT_APPROVED for Blanket/Contract PO
8903: */
8904: /* Bug# 1570115: While checking for Restricted items the query was
8905: ** checking with FSP.inventory_organization_id to instead of checking with the
9706:
9707: -- item is not null
9708: AND POL.item_id IS NOT NULL
9709:
9710: -- Document is standard PO
9711: AND POH.type_lookup_code = 'STANDARD'
9712:
9713: --VMI is enabled
9714: AND PASL.item_id = POL.item_id
9707: -- item is not null
9708: AND POL.item_id IS NOT NULL
9709:
9710: -- Document is standard PO
9711: AND POH.type_lookup_code = 'STANDARD'
9712:
9713: --VMI is enabled
9714: AND PASL.item_id = POL.item_id
9715: AND PASL.vendor_id = POH.vendor_id
9894: pol.po_line_id = poll.po_line_id AND
9895: pol.unit_price <> poll.price_override AND
9896: nvl(pol.cancel_flag,'N') = 'N' AND
9897: nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
9898: poll.shipment_type in ('STANDARD','PLANNED')
9899: )
9900: LOOP
9901: FND_MESSAGE.SET_NAME('PO','PO_PDOI_SHIP_PRICE_NE_LINE');
9902: FND_MESSAGE.SET_TOKEN('SHIP_PRICE', price_rec.price_override);
10098: ((POD.quantity_ordered * NVL(POLL.price_override,0)) +
10099: NVL(POD.nonrecoverable_tax,0)) * NVL(POD.rate,1),
10100: (POD.amount_ordered + NVL(POD.nonrecoverable_tax,0)) * NVL(POD.rate,1)
10101: )
10102: AND NVL(POD.DISTRIBUTION_TYPE, 'STANDARD') = 'STANDARD'
10103: ;
10104:
10105: ELSE
10106: l_progress := '031';
10158: PO_LINES_GT POL
10159: WHERE POD1.po_line_id = POL.po_line_id
10160: AND POD2.po_line_id = POL.po_line_id
10161: AND POL.po_header_id = p_document_id
10162: AND NVL(POD1.distribution_type, 'STANDARD') = 'STANDARD'
10163: AND NVL(POD2.distribution_type, 'STANDARD') = 'STANDARD'
10164: AND POD1.req_distribution_id IS NOT NULL -- Linked to PR Dists
10165: AND POD2.req_distribution_id IS NULL -- Not Linked to PR Dists
10166: GROUP BY POL.line_num
10159: WHERE POD1.po_line_id = POL.po_line_id
10160: AND POD2.po_line_id = POL.po_line_id
10161: AND POL.po_header_id = p_document_id
10162: AND NVL(POD1.distribution_type, 'STANDARD') = 'STANDARD'
10163: AND NVL(POD2.distribution_type, 'STANDARD') = 'STANDARD'
10164: AND POD1.req_distribution_id IS NOT NULL -- Linked to PR Dists
10165: AND POD2.req_distribution_id IS NULL -- Not Linked to PR Dists
10166: GROUP BY POL.line_num
10167: HAVING ((l_funds_change_amount BETWEEN 0 AND Sum(Nvl(POD2.funded_value,0)))
10271: AND exists(
10272: SELECT NULL
10273: FROM PO_DISTRIBUTIONS_GT POD
10274: WHERE POD.po_header_id = p_document_id
10275: AND NVL(POD.DISTRIBUTION_TYPE, 'STANDARD') = 'STANDARD'
10276: AND POD.req_distribution_id = PRD.distribution_id);
10277:
10278: ELSE
10279: l_progress := '034';
10378: FROM PO_HEADERS_GT POH,
10379: PO_LINES_GT POL
10380: WHERE POH.po_header_id = p_document_id
10381: AND POH.po_header_id = POL.po_header_id
10382: AND POH.type_lookup_code = 'STANDARD'
10383: AND Nvl(POH.clm_source_document_id, -1) <> Nvl(Nvl(POL.from_header_id,POL.contract_id), -1)
10384: AND nvl(l_clm_po,'N') = 'Y';
10385:
10386:
11203: END IF;
11204:
11205: END CHECK_BLANKET_AGREEMENT;
11206:
11207: --For Standard POs
11208: /**
11209: * Private Procedure: CHECK_STANDARD_PO
11210: * Requires:
11211: * IN PARAMETERS:
11205: END CHECK_BLANKET_AGREEMENT;
11206:
11207: --For Standard POs
11208: /**
11209: * Private Procedure: CHECK_STANDARD_PO
11210: * Requires:
11211: * IN PARAMETERS:
11212: * p_document_id: The requisition_header_id of submitted document
11213: * p_online_report_id: Id used to INSERT INTO online_report_text table
11216: * IN OUT PARAMETERS
11217: * p_sequence: Sequence number of last reported error
11218: * Modifies: Inserts error msgs in online_report_text_gt table, uses
11219: * global_temp tables for processing
11220: * Effects: This procedure runs the document submission checks for Standard
11221: * POs including GLOBAL AGREEMENTS reference checks and Consigned
11222: * Inventory checks
11223: * Returns:
11224: * p_sequence: This parameter contains the current count of number of error
11223: * Returns:
11224: * p_sequence: This parameter contains the current count of number of error
11225: * messages inserted
11226: */
11227: PROCEDURE check_standard_po(p_document_id IN NUMBER,
11228: p_online_report_id IN NUMBER,
11229: p_user_id IN NUMBER,
11230: p_login_id IN NUMBER,
11231: p_sequence IN OUT NOCOPY NUMBER,
11231: p_sequence IN OUT NOCOPY NUMBER,
11232: x_return_status OUT NOCOPY VARCHAR2) IS
11233:
11234: l_is_ga_referenced VARCHAR2(1) := NULL;
11235: l_api_name CONSTANT varchar2(40) := 'CHECK_STANDARD_PO';
11236: l_progress VARCHAR2(3);
11237:
11238: l_is_gc_referenced VARCHAR2(1) := NULL; --
11239: l_textline PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL; --< Shared Proc FPJ >
11263: BEGIN
11264:
11265: l_progress := '000';
11266:
11267: --check if atleast one line of Standard PO has Global Agreement reference
11268: --if so then call check_std_global_ref
11269: OPEN std_ga_ref_cursor(p_document_id);
11270:
11271: FETCH std_ga_ref_cursor INTO l_is_ga_referenced;
11360: l_api_name || '.others_exception', 'EXCEPTION: Location is '
11361: || l_progress || ' SQL CODE is '||sqlcode);
11362: END IF;
11363: END IF;
11364: END CHECK_STANDARD_PO;
11365:
11366:
11367: /**
11368: * Private Procedure: CHECK_STD_CONSIGNED_REF
11482: -- FROM po_lines_gt pol,
11483: -- po_line_locations_gt pll
11484: -- WHERE pol.po_header_id = p_document_id
11485: -- AND pol.po_line_id = pll.po_line_id
11486: -- AND pll.shipment_type = 'STANDARD'
11487: -- AND pll.consigned_flag = 'Y'
11488: -- AND nvl(pol.cancel_flag,'N') = 'N'
11489: -- AND nvl(pol.closed_code,'OPEN') <> 'FINALLY CLOSED'
11490: -- AND (pol.oke_contract_header_id is not null or
11548: WHERE POH.po_header_id = p_document_id AND
11549: POH.po_header_id = POL.po_header_id AND
11550: -- POH.po_header_id = PLL.po_header_id AND
11551: POL.po_line_id = PLL.po_line_id AND
11552: PLL.shipment_type = 'STANDARD' AND
11553: nvl(PLL.cancel_flag,'N') = 'N' AND
11554: nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED' AND
11555: -- Bug fix for #2733398
11556: -- nvl(PLL.approved_flag, 'N') IN ('N','R') AND
11693:
11694: END CHECK_STD_CONSIGNED_REF;
11695:
11696:
11697: --For Standard POs
11698: /**
11699: * Private Procedure: CHECK_STD_GLOBAL_REF
11700: * Requires:
11701: * IN PARAMETERS:
11706: * IN OUT PARAMETERS
11707: * p_sequence: Sequence number of last reported error
11708: * Modifies: Inserts error msgs in online_report_text_gt table, uses
11709: * global_temp tables for processing
11710: * Effects: This procedure runs the document submission checks for Standard
11711: * POs which have GLOBAL AGREEMENTS reference
11712: * Returns:
11713: * p_sequence: This parameter contains the current count of number of error
11714: * messages inserted
11813: || l_progress,'PO STD GA 2: Ref GA approved');
11814: END IF;
11815: END IF;
11816:
11817: --Check 2: If the standard PO line is referencing a GA then that GA
11818: --should be in an approved status.
11819: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
11820: --< Bug 3422733 > Only do this check if GA is not ON HOLD. The ON HOLD check
11821: -- is done later. Avoids showing 2 msgs for Contract that is ON HOLD.
12256:
12257: --bug11802312 Amount limit calculation should not include consigned shipments
12258: --Added this check as we are retaining the document reference on consigned shipments
12259: --as well.
12260: --Check 9: The total amount on all the standard PO SHIPMENT lines in the
12261: --current setup referencing the same GA should be less than the
12262: --amount limit on the GA header
12263:
12264: --
12303:
12304: -- bug3413891
12305: -- 1) Added optimizer hint FULL(POL)
12306: -- 2) Removed the join to PO_LINE_LOCATIONS_GT as it will bring in wrong
12307: -- result if the standard PO line has multiple shipments
12308:
12309: SELECT /*+ FULL(POL) */
12310: POL.line_num
12311: , nvl ( decode ( POL.quantity --
12414: --Check 10: If price override is yes then the line unit price cannot be more
12415: --than the price tolerance on the GA line.
12416: --
12417: -- Bug 3177525: This check should not be done if allow_price_override_flag
12418: -- is 'N' because the user can never modify the standard PO price/amount;
12419: -- it is automatically defaulted.
12420:
12421: INSERT into po_online_report_text_gt(online_report_id,
12422: last_update_login,
12501: || l_progress,'PO STD GA 11: Min Release Amount at line level check');
12502: END IF;
12503: END IF;
12504:
12505: --Check 11: The total amount on all the lines on the standard PO
12506: --referencing same GA line should be greater than the minimum release amount
12507: --on the GA line.
12508: l_textline := -- <2710030>
12509: FND_MESSAGE.GET_STRING('PO','PO_SUB_STD_GA_LINE_LESS_MINREL');
13141: || l_progress,'PA CONTRACT 1: Released Amount exceeds Amount Limit');
13142: END IF;
13143: END IF;
13144:
13145: -- Check 1: The amount of all standard purchase orders
13146: -- for a contract should not exceed the amount limit of the contract.
13147: -- PO_SUB_CONTRACT_AMT_LIMIT
13148: --
13149: --instead of summing up the Line Loc values. The results should be
13439: -- Draft Id of the Modification Document
13440: -- Added this parameter for CLM Aprvl
13441: --OUT:
13442: --x_return_status
13443: -- APPS standard parameter.
13444: --Testing:
13445: --
13446: --End of Comments
13447: -------------------------------------------------------------------------------
13736: amount)
13737: --
13738: WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i);
13739:
13740: --To propogate line price change to shipment level for Standard PO
13741: IF (p_document_type = 'PO' AND p_document_subtype = 'STANDARD') THEN
13742:
13743: l_progress := '002';
13744: -- SQL What: Setting the priceoverride at Shipment level
13737: --
13738: WHERE po_line_id = p_requested_changes.line_changes.po_line_id(i);
13739:
13740: --To propogate line price change to shipment level for Standard PO
13741: IF (p_document_type = 'PO' AND p_document_subtype = 'STANDARD') THEN
13742:
13743: l_progress := '002';
13744: -- SQL What: Setting the priceoverride at Shipment level
13745: -- SQL Why: Need to propogate line price change to shipment level
13742:
13743: l_progress := '002';
13744: -- SQL What: Setting the priceoverride at Shipment level
13745: -- SQL Why: Need to propogate line price change to shipment level
13746: -- for Standard PO
13747: -- SQL Join: po_line_id
13748: FORALL i IN 1..p_requested_changes.line_changes.po_line_id.COUNT
13749: UPDATE po_line_locations_gt
13750: SET price_override = nvl(p_requested_changes.line_changes.unit_price(i),price_override)
14363: CLM_COTR_OFFICE ,
14364: CLM_COTR_CONTACT ,
14365: CLM_PRIORITY_CODE ,
14366: CLM_MOD_ISSUING_OFFICE ,
14367: CLM_STANDARD_FORM ,
14368: CLM_DOCUMENT_FORMAT ,
14369: AME_TRANSACTION_TYPE ,
14370: CLM_AWARD_TYPE ,
14371: CLM_SOURCE_DOCUMENT_ID ,
14527: CLM_COTR_OFFICE ,
14528: CLM_COTR_CONTACT ,
14529: CLM_PRIORITY_CODE ,
14530: CLM_MOD_ISSUING_OFFICE ,
14531: CLM_STANDARD_FORM ,
14532: CLM_DOCUMENT_FORMAT ,
14533: AME_TRANSACTION_TYPE ,
14534: CLM_AWARD_TYPE ,
14535: CLM_SOURCE_DOCUMENT_ID ,
15986:
15987: BEGIN
15988:
15989: l_progress := '000';
15990: -- Standard call to check for call compatibility
15991: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
15992: THEN
15993: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
15994: END IF;
16060: p_employee_id || '=' || p_owner_id_column ||')';
16061: /* OR (' ||
16062: 'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
16063: 'WHERE POAH2.employee_id = ' || p_employee_id ||
16064: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
16065: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || '))))';*/
16066:
16067: x_where_clause := x_where_clause ||
16068: ' OR '||'('||p_query_table || '.security_level_code = ''HIERARCHY'' AND '||
16069: '((' || p_employee_id || '=' || p_owner_id_column ||') OR (' ||
16070: 'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
16071: 'WHERE POAH2.employee_id =' || p_employee_id ||
16072: /* Bug 7229262/7239696
16073: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
16074: */
16075: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
16076: /* End Bug 7229262/7239696 */
16077:
16071: 'WHERE POAH2.employee_id =' || p_employee_id ||
16072: /* Bug 7229262/7239696
16073: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
16074: */
16075: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) )' ||
16076: /* End Bug 7229262/7239696 */
16077:
16078: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
16079: p_employee_id || ' IN (SELECT H.superior_id ' ||
16088: '((' || p_employee_id || '=' || p_owner_id_column ||') OR (' ||
16089: 'EXISTS (SELECT ''Y'' FROM PO_ACTION_HISTORY POAH2 ' ||
16090: 'WHERE POAH2.employee_id =' || p_employee_id ||
16091: /* Bug 7229262/7239696
16092: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
16093: */
16094: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
16095: /* End Bug 7229262/7239696 */
16096: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
16090: 'WHERE POAH2.employee_id =' || p_employee_id ||
16091: /* Bug 7229262/7239696
16092: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.type_lookup_code, ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
16093: */
16094: ' AND POAH2.object_type_code = (DECODE(' || p_query_table || '.'||l_doctype_column||', ''BLANKET'', ''PA'', ''STANDARD'', ''PO'' , ''PLANNED'' , ''PO'' , ''CONTRACT'' , ''PA'', ''RELEASE'' , ''RELEASE'' ) ) ' ||
16095: /* End Bug 7229262/7239696 */
16096: ' AND POAH2.object_id = ' || p_query_table || '.' || l_id_column || ')) OR (' ||
16097: 'EXISTS(SELECT NULL FROM PO_AGENTS WHERE agent_id= ' ||
16098: p_employee_id || ' AND SYSDATE BETWEEN NVL(start_date_active, ' ||
17005: || l_progress, 'Entering Procedure '||l_api_name);
17006: END IF;
17007: END IF;
17008:
17009: -- Standard call to check for call compatibility
17010: l_progress := '010';
17011: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
17012: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
17013: END IF;
18948: BEGIN
18949:
18950: d_progress := 10;
18951:
18952: IF (((p_document_type = g_document_type_PO) AND (p_document_subtype = 'STANDARD'))
18953: OR (p_document_type = g_document_type_RELEASE))
18954: THEN
18955:
18956: d_progress := 20;
20266: FROM po_lines_gt pol, po_line_locations_gt poll
20267: , po_distributions_gt pod, gl_code_combinations gcc
20268: WHERE pod.line_location_id = poll.line_location_id
20269: AND pol.po_line_id = poll.po_line_id
20270: AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'PREPAYMENT') --
20272: AND ( NVL(poll.cancel_flag, 'N') = 'N' OR p_action_requested = g_action_FINAL_CLOSE_CHECK)
20273: AND gcc.code_combination_id = pod.code_combination_id
20274: AND NVL(p_action_date, trunc(SYSDATE)) NOT BETWEEN
20775: PO_LINE_LOCATIONS_GT PLL
20776: , PO_LINES_GT POL
20777: WHERE
20778: POL.po_line_id = PLL.po_line_id
20779: AND PLL.shipment_type in ('STANDARD', 'PLANNED')
20780: AND POL.po_header_id = p_document_id
20781: AND nvl(POL.cancel_flag,'N') = 'N'
20782: AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
20783: AND (
20786: (SELECT round(sum(PLL2.quantity) -
20787: sum(nvl(PLL2.quantity_cancelled, 0)), 10)
20788: FROM PO_LINE_LOCATIONS_GT PLL2
20789: WHERE PLL2.po_line_id = POL.po_line_id AND
20790: PLL2.shipment_type in ('STANDARD', 'PLANNED') ) )
20791: )
20792: OR --
20793: ( ( POL.amount IS NOT NULL )
20794: AND ( round(POL.amount, 10) <>
20797: , 10
20798: )
20799: FROM po_line_locations_gt PLL3
20800: WHERE PLL3.po_line_id = POL.po_line_id
20801: AND PLL3.shipment_type IN ('STANDARD','PLANNED')
20802: )
20803: )
20804: )
20805: )
20894: l_progress := '023';
20895:
20896: --Bug 5440038 , included ADVANCES for roll-up logic but this would apply
20897: -- only to financing case, as the for actual case only the
20898: -- STANDARD line locations are rolled up
20899: -- for a financing case, the line location rollups would include all pay items
20900: -- and the advance amounts which together make up the prepayment amount
20901: -- should be less than the line amount
20902: -- the financing_advance_amount advance amount is also calculated for
20961: PO_LINE_LOCATIONS_GT PLL
20962: , PO_LINES_GT POL
20963: WHERE
20964: POL.po_line_id = PLL.po_line_id
20965: AND ( (l_is_financing_flag = 'N' AND PLL.shipment_type = 'STANDARD')
20966: OR (l_is_financing_flag = 'Y' and PLL.shipment_type = 'PREPAYMENT'))
20967: AND POL.po_header_id = p_document_id
20968: AND nvl(POL.cancel_flag,'N') = 'N'
20969: AND nvl(POL.closed_code,'OPEN') <> 'FINALLY CLOSED'
21093: AND POD.line_location_id = PLL.line_location_id
21094: AND PLL.po_header_id = p_document_id
21095: AND nvl(PLL.cancel_flag,'N') = 'N'
21096: AND nvl(PLL.closed_code,'OPEN') <> 'FINALLY CLOSED'
21097: AND PLL.shipment_type in ('STANDARD', 'PLANNED', 'PREPAYMENT') --
21099: POL.line_num
21100: , PLL.shipment_num
21101: , PLL.quantity
21397: x_invoice_type => l_invoice_type ,
21398: x_calling_sequence => l_calling_sequence,
21399: x_return_status => l_return_status);
21400:
21401: IF p_document_type = 'PO' AND p_document_subtype = 'STANDARD'
21402: AND (p_document_level = g_document_level_HEADER
21403: OR p_document_level = g_document_level_LINE) THEN
21404: --
21405: -- Handle Header final close / Unreserve SPO
21632: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
21633: PO_LOG.proc_begin(d_module, 'p_doc_level_id', p_doc_level_id);
21634: END IF;
21635:
21636: IF p_document_subtype = 'STANDARD' THEN
21637: IF p_document_level = g_document_level_HEADER THEN
21638: l_document_id := p_doc_level_id;
21639: ELSIF p_document_level = g_document_level_LINE THEN
21640: SELECT po_header_id
22284: IF itemtype is not NULL and itemkey is not NULL THEN
22285: PO_DEBUG.debug_stmt(l_log_head , l_progress, 'ledger currency '||l_currency_code);
22286: END IF;
22287:
22288: IF l_type_lookup_code = 'STANDARD' THEN
22289: IF p_draft_id is NULL or p_draft_id = -1 THEN
22290: -- Finding the document amount
22291: -- When warrant currency is same as doc currency get the amount in doc currency
22292: l_doc_amount := po_document_totals_pvt.getAmountOrdered(
22314: FROM po_headers_draft_all phm
22315: WHERE phm.po_header_id = p_header_id
22316: AND phm.draft_id = p_draft_id;
22317: END IF;
22318: END IF; -- l_type_lookup_code = 'STANDARD' THEN
22319: IF l_doc_currency <> l_warrant_currency AND
22320: l_warrant_currency = l_currency_code THEN
22321: -- When warrant currency is same as ledger currency convert the amount
22322: l_doc_amount := l_doc_amount * l_rate;
22802: PO_LINE_LOCATIONS_GT PLL,
22803: PO_LINES_GT POL
22804: WHERE POD.CODE_COMBINATION_ID IS NULL
22805: AND POD.PO_HEADER_ID = P_DOCUMENT_ID
22806: AND NVL(POD.DISTRIBUTION_TYPE, 'STANDARD') <> 'AGREEMENT'
22807: AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
22808: AND PLL.PO_LINE_ID = POL.PO_LINE_ID;
22809:
22810: P_SEQUENCE := P_SEQUENCE + SQL%ROWCOUNT;
22851: PO_LINE_LOCATIONS_GT PLL,
22852: PO_LINES_GT POL
22853: WHERE POD.BUDGET_ACCOUNT_ID IS NULL
22854: AND POD.PO_HEADER_ID = P_DOCUMENT_ID
22855: AND NVL(POD.DISTRIBUTION_TYPE, 'STANDARD') <> 'AGREEMENT'
22856: AND Nvl(PREVENT_ENCUMBRANCE_FLAG,'N') = 'N'
22857: AND POD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
22858: AND PLL.PO_LINE_ID = POL.PO_LINE_ID;
22859:
23555: po_distributions_gt pd
23556: WHERE 1=1
23557: AND ph.po_header_id = pl.po_header_id
23558: AND pl.po_line_id=pll.po_line_id
23559: AND ph.type_lookup_code = 'STANDARD'
23560: AND pll.line_location_id = pd.line_location_id
23561: AND nvl(pll.approved_flag,'N') <> 'Y'
23562: AND nvl(pll.cancel_flag,'N') <> 'Y'
23563: AND nvl(pll.closed_code,'OPEN') NOT IN ('FINALLY CLOSED' , 'CLOSED')
24222: AND NVL( POLL.APPROVED_FLAG, 'N' ) <> 'Y'
24223: AND NVL( POLL.CANCEL_FLAG, 'N' ) <> 'Y'
24224: AND NVL( POLL.CLOSED_CODE, 'OPEN' ) <> 'FINALLY CLOSED'
24225: ---
24226: AND POD.distribution_type = 'STANDARD'
24227: ---
24228:
24229: AND( POD.budget_account_id IS NULL
24230: OR Validate_account_wrapper(
26564:
26565: SELECT ( CASE
26566: WHEN phda.type_lookup_code = 'BLANKET' THEN 'PA_BLANKET_MOD'
26567: WHEN phda.type_lookup_code = 'CONTRACT' THEN 'PA_CONTRACT_MOD'
26568: ELSE 'PO_STANDARD_MOD'
26569: END ) OKC_DOC_TYPE,
26570: (CASE WHEN phda.type_lookup_code = 'BLANKET' OR phda.type_lookup_code = 'CONTRACT' THEN
26571: Nvl(phda.amount_limit,0) - Nvl(phd.amount_limit,0)
26572: ELSE