[Home] [Help]
264: -- This procedure is dependent on the Contracts group API OKC_TERMS_QA_GRP and
265: -- calls procedure qa_doc. It also subtypes the qa_result_tbl_type to store
266: -- returned error messages.
267: --Modifies:
268: -- po_online_report_text_gt
269: --Locks:
270: -- None.
271: --Function:
272: -- This procedure is used to validate contract articles and deliverables and is
400: l_progress := '002';
401: l_row_index := l_qa_result_tbl.FIRST;
402: LOOP
403: EXIT WHEN l_row_index IS NULL;
404: INSERT INTO po_online_report_text_gt
405: (online_report_id
406: ,last_updated_by
407: ,last_update_date
408: ,created_by
558:
559: l_online_report_id NUMBER;
560: l_user_id po_lines.last_updated_by%TYPE := -1;
561: l_login_id po_lines.last_update_login%TYPE := -1;
562: p_sequence po_online_report_text.sequence%TYPE :=0;
563:
564: l_return_status varchar2(1);
565:
566: l_conterms_exist_flag VARCHAR2(1); --
662: END IF;
663:
664: l_progress := '003';
665: --Get the unique id to be used for this document
666: SELECT PO_ONLINE_REPORT_TEXT_S.nextval
667: INTO l_online_report_id
668: FROM sys.dual;
669:
670: l_progress := '004';
1283: SELECT count(text_line),
1284: COUNT(DECODE(message_type, 'W', 'W', NULL))
1285: INTO l_num_messages,
1286: l_num_warnings
1287: FROM po_online_report_text_gt
1288: WHERE online_report_id = l_online_report_id;
1289:
1290:
1291: l_progress := '026';
1314: x_has_warnings := FND_API.G_TRUE;
1315: END IF;
1316:
1317: --Bulk update online_report_text table with errors
1318: INSERT INTO po_online_report_text(online_report_id,
1319: last_update_login,
1320: last_updated_by,
1321: last_update_date,
1322: created_by,
1338: distribution_num,
1339: sequence,
1340: text_line,
1341: message_type --
1342: FROM po_online_report_text_gt
1343: WHERE online_report_id = x_online_report_id;
1344:
1345: select count(*) into l_num_messages from po_online_report_text_gt where online_report_id = x_online_report_id;
1346:
1341: message_type --
1342: FROM po_online_report_text_gt
1343: WHERE online_report_id = x_online_report_id;
1344:
1345: select count(*) into l_num_messages from po_online_report_text_gt where online_report_id = x_online_report_id;
1346:
1347: l_progress := '027';
1348:
1349: -- SQL What: Gets the relevent messages from global temp table
1357: x_doc_check_error_record.shipment_num,
1358: x_doc_check_error_record.distribution_num,
1359: x_doc_check_error_record.message_name,
1360: x_doc_check_error_record.message_type --
1361: FROM po_online_report_text_gt
1362: WHERE online_report_id = x_online_report_id;
1363:
1364: ELSE
1365: x_sub_check_status := FND_API.G_RET_STS_SUCCESS;
1448: l_num_errors NUMBER := 0;
1449: l_online_report_id NUMBER;
1450: l_user_id PO_LINES.last_updated_by%TYPE := -1;
1451: l_login_id PO_LINES.last_update_login%TYPE := -1;
1452: p_sequence PO_ONLINE_REPORT_TEXT.sequence%TYPE :=0;
1453:
1454: l_return_status VARCHAR2(1);
1455:
1456: BEGIN
1497:
1498: l_progress := '003';
1499:
1500: --Get the unique id to be used for this document
1501: SELECT PO_ONLINE_REPORT_TEXT_S.nextval
1502: INTO l_online_report_id
1503: FROM sys.dual;
1504:
1505: l_progress := '004';
1538:
1539: --Done with CHECKS, now prepare OUT parameters to pass back
1540: SELECT count(text_line)
1541: INTO l_num_errors
1542: FROM po_online_report_text_gt
1543: WHERE online_report_id = l_online_report_id;
1544:
1545: l_progress := '008';
1546:
1558:
1559: x_sub_check_status := FND_API.G_RET_STS_ERROR;
1560:
1561: --Bulk update online_report_text table with errors
1562: INSERT INTO po_online_report_text(
1563: online_report_id,
1564: last_update_login,
1565: last_updated_by,
1566: last_update_date,
1581: shipment_num,
1582: distribution_num,
1583: sequence,
1584: text_line
1585: FROM po_online_report_text_gt
1586: WHERE online_report_id = x_online_report_id;
1587:
1588: l_progress := '009';
1589:
1667: p_login_id IN NUMBER,
1668: p_sequence IN OUT NOCOPY NUMBER,
1669: x_return_status OUT NOCOPY VARCHAR2) IS
1670:
1671: l_textline po_online_report_text.text_line%TYPE := NULL;
1672: l_api_name CONSTANT varchar2(40) := 'CHECK_REQUISITIONS';
1673: l_progress VARCHAR2(3);
1674:
1675: TYPE NumTab is TABLE of NUMBER INDEX by BINARY_INTEGER;
1712:
1713: -- Check 1: Each Requisition Header must have atleast one line
1714: -- PO_SUB_REQ_HEADER_NO_LINES
1715: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REQ_HEADER_NO_LINES');
1716: INSERT INTO po_online_report_text_gt(online_report_id,
1717: last_update_login,
1718: last_updated_by,
1719: last_update_date,
1720: created_by,
1759: -- Check 2: Each Requisition line must have atleast one distribution
1760: -- PO_SUB_REQ_LINE_NO_DIST
1761:
1762: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REQ_LINE_NO_DIST');
1763: INSERT INTO po_online_report_text_gt(online_report_id,
1764: last_update_login,
1765: last_updated_by,
1766: last_update_date,
1767: created_by,
1853: l_rowCount(i) := i;
1854: END LOOP;
1855:
1856: FORALL i IN 1..l_line_num.COUNT
1857: INSERT INTO po_online_report_text_gt (online_report_id,
1858: last_update_login,
1859: last_updated_by,
1860: last_update_date,
1861: created_by,
1919: -- Check 4:Lines with SOURCE type as INVENTORY can have one only one dist
1920: -- PO_SUB_REQ_SOURCE_ONE_DIST
1921:
1922: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REQ_SOURCE_ONE_DIST');
1923: INSERT INTO po_online_report_text_gt(online_report_id,
1924: last_update_login,
1925: last_updated_by,
1926: last_update_date,
1927: created_by,
1967:
1968: -- Check 5: Requistion Rate cannot be NULL if using a foreign currency vendor
1969: -- PO_SUB_REQ_RATE_NULL
1970: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REQ_RATE_NULL');
1971: INSERT INTO po_online_report_text_gt(online_report_id,
1972: last_update_login,
1973: last_updated_by,
1974: last_update_date,
1975: created_by,
2055:
2056: -- Check 7: Requisitions cannot have ATO/CTO model items (Bug 3362369)
2057: -- PO_ATO_ITEM_NA
2058: l_textline := FND_MESSAGE.GET_STRING('PO','PO_ATO_ITEM_NA');
2059: INSERT INTO po_online_report_text_gt(online_report_id,
2060: last_update_login,
2061: last_updated_by,
2062: last_update_date,
2063: created_by,
2134:
2135: l_tax_message := fnd_message.get_string('PO','PO_TAX_CALCULATION')||' : ' ;
2136:
2137: FORALL i IN 1..po_tax_interface_pvt.G_TAX_ERRORS_TBL.MESSAGE_TEXT.COUNT
2138: INSERT INTO po_online_report_text_gt
2139: (
2140: online_report_id,
2141: last_update_login,
2142: last_updated_by,
2246: p_login_id IN NUMBER,
2247: p_sequence IN OUT NOCOPY NUMBER,
2248: x_return_status OUT NOCOPY VARCHAR2) IS
2249:
2250: l_textline po_online_report_text.text_line%TYPE := NULL;
2251: l_api_name CONSTANT varchar2(40) := 'CHECK_RELEASES';
2252: l_progress VARCHAR2(3);
2253:
2254: --
2310: --< Bug 3422733 > Only do this check if the BPA is not ON HOLD. The ON HOLD
2311: -- check is done later. Avoids showing 2 msgs for BPA that is ON HOLD.
2312:
2313: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_PA_APPROVED');
2314: INSERT INTO po_online_report_text_gt(online_report_id,
2315: last_update_login,
2316: last_updated_by,
2317: last_update_date,
2318: created_by,
2361: -- 1) Release has not been approved once yet, AND
2362: -- 2) SYSDATE is after expiration date on the blanket header
2363:
2364: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_PA_EXPIRED');
2365: INSERT INTO po_online_report_text_gt(online_report_id,
2366: last_update_login,
2367: last_updated_by,
2368: last_update_date,
2369: created_by,
2409: -- on hold
2410: -- PO_SUB_REL_PA_ON_HOLD
2411:
2412: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_PA_ON_HOLD');
2413: INSERT INTO po_online_report_text_gt(online_report_id,
2414: last_update_login,
2415: last_updated_by,
2416: last_update_date,
2417: created_by,
2455: -- Check 4: Associated Purchase Agreement's supplier should not be on hold
2456: -- PO_SUB_REL_VENDOR_ON_HOLD
2457:
2458: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_VENDOR_ON_HOLD');
2459: INSERT INTO po_online_report_text_gt(online_report_id,
2460: last_update_login,
2461: last_updated_by,
2462: last_update_date,
2463: created_by,
2504: -- Check 5: Every Release must have atleast one shipment
2505: -- PO_SUB_REL_NO_SHIP
2506:
2507: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_NO_SHIP');
2508: INSERT INTO po_online_report_text_gt(online_report_id,
2509: last_update_login,
2510: last_updated_by,
2511: last_update_date,
2512: created_by,
2574: l_rowCount(i) := i;
2575: END LOOP;
2576:
2577: FORALL i IN 1..l_line_num.COUNT
2578: INSERT INTO po_online_report_text_gt (
2579: online_report_id,
2580: last_update_login,
2581: last_updated_by,
2582: last_update_date,
2630: -- Bug 3177525: This check should not be done if allow_price_override_flag
2631: -- is 'N' because the user can never modify the release price/amount; it is
2632: -- automatically defaulted.
2633:
2634: INSERT INTO po_online_report_text_gt(online_report_id,
2635: last_update_login,
2636: last_updated_by,
2637: last_update_date,
2638: created_by,
2748: l_rowCount(i) := i;
2749: END LOOP;
2750:
2751: FORALL i IN 1..l_line_num.COUNT
2752: INSERT INTO po_online_report_text_gt(online_report_id,
2753: last_update_login,
2754: last_updated_by,
2755: last_update_date,
2756: created_by,
2807: -- Check 8: All Release shipments should have atleast one distribution
2808: -- PO_SUB_REL_SHIP_NO_DIST
2809:
2810: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_SHIP_NO_DIST');
2811: INSERT INTO po_online_report_text_gt(online_report_id,
2812: last_update_login,
2813: last_updated_by,
2814: last_update_date,
2815: created_by,
2859: -- currency vendor. We are using foreign currency if the currency on PO
2860: -- Header does not match Set of Books currency
2861: -- PO_SUB_REL_RATE_NULL
2862: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_RATE_NULL');
2863: INSERT INTO po_online_report_text_gt(online_report_id,
2864: last_update_login,
2865: last_updated_by,
2866: last_update_date,
2867: created_by,
2918: -- minimum release amount
2919: -- PO_SUB_REL_AMT_LESS_MINREL_AMT
2920:
2921: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_AMT_LESS_MINREL_AMT');
2922: INSERT INTO po_online_report_text_gt(online_report_id,
2923: last_update_login,
2924: last_updated_by,
2925: last_update_date,
2926: created_by,
3032: --
3033:
3034:
3035: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REL_AMT_GRT_LIMIT_AMT');
3036: INSERT INTO po_online_report_text_gt (online_report_id,
3037: last_update_login,
3038: last_updated_by,
3039: last_update_date,
3040: created_by,
3130: l_rowCount(i) := i;
3131: END LOOP;
3132:
3133: FORALL i IN 1..l_line_num.COUNT
3134: INSERT INTO po_online_report_text_gt(online_report_id,
3135: last_update_login,
3136: last_updated_by,
3137: last_update_date,
3138: created_by,
3213: --'Shipment#
3214: -- is disabled
3215: -- Bug #1630662
3216: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_UOM_CLASS_CONVERSION');
3217: INSERT INTO po_online_report_text_gt(online_report_id,
3218: last_update_login,
3219: last_updated_by,
3220: last_update_date,
3221: created_by,
3276: atlease one approved ASL either at item level or at category level
3277: if there is no item ASL*/
3278:
3279: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ITEM_NOT_APPROVED_REL');
3280: INSERT INTO po_online_report_text_gt(online_report_id,
3281: last_update_login,
3282: last_updated_by,
3283: last_update_date,
3284: created_by,
3378: time items as there will not be any record
3379: */
3380:
3381: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ITEM_ASL_DEBARRED_REL');
3382: INSERT INTO po_online_report_text_gt(online_report_id,
3383: last_update_login,
3384: last_updated_by,
3385: last_update_date,
3386: created_by,
3461: -- Check 17: ATO/CTO Model items not allowed on Releases (Bug 3362369)
3462:
3463: l_textline := FND_MESSAGE.get_string('PO', 'PO_ATO_ITEM_NA');
3464:
3465: INSERT INTO po_online_report_text_gt(
3466: online_report_id,
3467: last_update_login,
3468: last_updated_by,
3469: last_update_date,
3520:
3521: -- Check 018: Item has to purchasable
3522:
3523: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_ALL_NO_ITEM');
3524: INSERT INTO po_online_report_text_gt(online_report_id,
3525: last_update_login,
3526: last_updated_by,
3527: last_update_date,
3528: created_by,
3573: -- Check 19: Release should not be on hold (Bug 3678912)
3574: -- PO_ON_HOLD_CANNOT_APPROVE
3575:
3576: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_ON_HOLD_CANNOT_APPROVE');
3577: INSERT INTO po_online_report_text_gt(online_report_id,
3578: last_update_login,
3579: last_updated_by,
3580: last_update_date,
3581: created_by,
3626: fnd_message.set_token('VALUE', to_char(l_vendor_id), FALSE);
3627: l_textline := substr(fnd_message.get, 1, 240);
3628:
3629:
3630: INSERT INTO po_online_report_text_gt(online_report_id,
3631: last_update_login,
3632: last_updated_by,
3633: last_update_date,
3634: created_by,
3671: fnd_message.set_name('PO', 'PO_PDOI_INVALID_VENDOR_SITE');
3672: fnd_message.set_token('VALUE', to_char(l_vendor_site_id), FALSE);
3673: l_textline := substr(fnd_message.get, 1, 240);
3674:
3675: INSERT INTO po_online_report_text_gt(online_report_id,
3676: last_update_login,
3677: last_updated_by,
3678: last_update_date,
3679: created_by,
3715: fnd_message.set_name('PO', 'PO_PDOI_INVALID_VDR_CNTCT');
3716: fnd_message.set_token('VALUE', to_char(l_vendor_contact_id), FALSE);
3717: l_textline := substr(fnd_message.get, 1, 240);
3718:
3719: INSERT INTO po_online_report_text_gt(online_report_id,
3720: last_update_login,
3721: last_updated_by,
3722: last_update_date,
3723: created_by,
3794:
3795: l_tax_message := fnd_message.get_string('PO','PO_TAX_CALCULATION')||' : ' ;
3796:
3797: FORALL i IN 1..po_tax_interface_pvt.G_TAX_ERRORS_TBL.MESSAGE_TEXT.COUNT
3798: INSERT INTO po_online_report_text_gt
3799: (
3800: online_report_id,
3801: last_update_login,
3802: last_updated_by,
3864: END IF;
3865: END IF;
3866: -- Check 25: Validation for LCM enabled release to check whether its shipment has invoice match option as 'Receipt'
3867: l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_REL_SHIP_INV_MATCH_NE_R');
3868: INSERT INTO po_online_report_text_gt (online_report_id,
3869: last_update_login,
3870: last_updated_by,
3871: last_update_date,
3872: created_by,
3908: END IF;
3909: END IF;
3910: -- Check 26: Validation for LCM enabled release to check whether its distribution has destination type as 'Inventory'
3911: l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_REL_DIST_DEST_TYPE_NE_I');
3912: INSERT INTO po_online_report_text_gt (online_report_id,
3913: last_update_login,
3914: last_updated_by,
3915: last_update_date,
3916: created_by,
3999: p_login_id IN NUMBER,
4000: p_sequence IN OUT NOCOPY NUMBER,
4001: x_return_status OUT NOCOPY VARCHAR2) IS
4002:
4003: l_textline po_online_report_text.text_line%TYPE := NULL;
4004: l_api_name CONSTANT varchar2(40) := 'CHECK_PO_REL_REQPRICE';
4005: l_progress VARCHAR2(3);
4006:
4007: l_enforce_price_tolerance po_system_parameters.enforce_price_change_allowance%TYPE;
4542: l_progress := '009';
4543: --Report the price tolerance error
4544: l_textline := FND_MESSAGE.GET_STRING('PO',
4545: 'PO_SUB_REQ_PRICE_TOL_EXCEED');
4546: INSERT into po_online_report_text_gt(
4547: online_report_id,
4548: last_update_login,
4549: last_updated_by,
4550: last_update_date,
4719: l_progress := '012';
4720: --Report the price amount exceeded error
4721: l_textline :=
4722: FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REQ_AMT_TOL_EXCEED');
4723: INSERT into po_online_report_text_gt(
4724: online_report_id,
4725: last_update_login,
4726: last_updated_by,
4727: last_update_date,
4811: p_login_id IN NUMBER,
4812: p_sequence IN OUT NOCOPY NUMBER,
4813: x_return_status OUT NOCOPY VARCHAR2) IS
4814:
4815: l_textline po_online_report_text.text_line%TYPE := NULL;
4816: l_api_name CONSTANT varchar2(40) := 'CHECK_PO_PA_HEADER';
4817: l_progress VARCHAR2(3);
4818:
4819: l_vendor_id po_headers.vendor_id%TYPE;
4848: -- Check 1: Purchase Order vendor should not be on hold
4849: -- PO_SUB_VENDOR_ON_HOLD
4850:
4851: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_VENDOR_ON_HOLD');
4852: INSERT INTO po_online_report_text_gt(online_report_id,
4853: last_update_login,
4854: last_updated_by,
4855: last_update_date,
4856: created_by,
4922: -- Check 2: When no rows are returned, its likely that there
4923: --are problems with system setup
4924: --
4925: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_SYSTEM_SETUP');
4926: INSERT INTO po_online_report_text_gt(online_report_id,
4927: last_update_login,
4928: last_updated_by,
4929: last_update_date,
4930: created_by,
4969:
4970: --Check 3: vendor_id is NULL
4971: IF l_vendor_id IS NULL THEN
4972: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ENTER_VENDOR');
4973: INSERT INTO po_online_report_text_gt(online_report_id,
4974: last_update_login,
4975: last_updated_by,
4976: last_update_date,
4977: created_by,
5009:
5010: --Check 4: vendor_site_id is NULL
5011: IF l_vendor_site_id IS NULL THEN
5012: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ENTER_VENDOR_SITE');
5013: INSERT INTO po_online_report_text_gt(online_report_id,
5014: last_update_login,
5015: last_updated_by,
5016: last_update_date,
5017: created_by,
5049:
5050: --Check 5: ship_to_location_id is NULL
5051: IF l_ship_to_location_id IS NULL THEN
5052: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_NO_SHIP_TO_LOC_ID');
5053: INSERT INTO po_online_report_text_gt(online_report_id,
5054: last_update_login,
5055: last_updated_by,
5056: last_update_date,
5057: created_by,
5089: AND NVL(TRUNC(hla.inactive_date), TRUNC(SYSDATE)+1 ) <= TRUNC(SYSDATE)
5090: AND hlt.location_id=hla.location_id
5091: AND hlt.language=USERENV('LANG');
5092:
5093: INSERT INTO po_online_report_text_gt(online_report_id,
5094: last_update_login,
5095: last_updated_by,
5096: last_update_date,
5097: created_by,
5140: --Check 6: bill_to_location_id is NULL
5141: IF l_bill_to_location_id IS NULL THEN
5142:
5143: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_NO_BILL_TO_LOC_ID');
5144: INSERT INTO po_online_report_text_gt(online_report_id,
5145: last_update_login,
5146: last_updated_by,
5147: last_update_date,
5148: created_by,
5180: AND NVL(TRUNC(hla.inactive_date), TRUNC(SYSDATE)+1 ) <= TRUNC(SYSDATE)
5181: AND hlt.location_id=hla.location_id
5182: AND hlt.language=USERENV('LANG');
5183:
5184: INSERT INTO po_online_report_text_gt(online_report_id,
5185: last_update_login,
5186: last_updated_by,
5187: last_update_date,
5188: created_by,
5227:
5228: --Check 7: currency_code is NULL
5229: IF l_currency_code IS NULL THEN
5230: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_NO_CURRENCY_CODE');
5231: INSERT INTO po_online_report_text_gt(online_report_id,
5232: last_update_login,
5233: last_updated_by,
5234: last_update_date,
5235: created_by,
5270: (l_rate_type IS NULL OR l_rate IS NULL OR
5271: (l_rate_type <> 'User' AND l_rate_date IS NULL)) -- Bug 3759198
5272: THEN
5273: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_NO_RATE_TYPE');
5274: INSERT INTO po_online_report_text_gt(online_report_id,
5275: last_update_login,
5276: last_updated_by,
5277: last_update_date,
5278: created_by,
5317: IF ( l_rate_type = 'User' ) THEN
5318:
5319: l_textline := FND_MESSAGE.get_string('PO','PO_SUB_USER_RATE_TYPE');
5320:
5321: INSERT INTO po_online_report_text_gt
5322: ( online_report_id
5323: , last_update_login
5324: , last_updated_by
5325: , last_update_date
5370: -- Check 10: Purchase Order should not be on hold (Bug 3678912)
5371: -- PO_ON_HOLD_CANNOT_APPROVE
5372:
5373: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_ON_HOLD_CANNOT_APPROVE');
5374: INSERT INTO po_online_report_text_gt(online_report_id,
5375: last_update_login,
5376: last_updated_by,
5377: last_update_date,
5378: created_by,
5422: fnd_message.set_token('VALUE', to_char(l_vendor_id), FALSE);
5423: l_textline := substr(fnd_message.get, 1, 240);
5424:
5425:
5426: INSERT INTO po_online_report_text_gt(online_report_id,
5427: last_update_login,
5428: last_updated_by,
5429: last_update_date,
5430: created_by,
5466: fnd_message.set_name('PO', 'PO_PDOI_INVALID_VENDOR_SITE');
5467: fnd_message.set_token('VALUE', to_char(l_vendor_site_id), FALSE);
5468: l_textline := substr(fnd_message.get, 1, 240);
5469:
5470: INSERT INTO po_online_report_text_gt(online_report_id,
5471: last_update_login,
5472: last_updated_by,
5473: last_update_date,
5474: created_by,
5509: fnd_message.set_name('PO', 'PO_PDOI_INVALID_VDR_CNTCT');
5510: fnd_message.set_token('VALUE', to_char(l_vendor_contact_id), FALSE);
5511: l_textline := substr(fnd_message.get, 1, 240);
5512:
5513: INSERT INTO po_online_report_text_gt(online_report_id,
5514: last_update_login,
5515: last_updated_by,
5516: last_update_date,
5517: created_by,
5597: p_login_id IN NUMBER,
5598: p_sequence IN OUT NOCOPY NUMBER,
5599: x_return_status OUT NOCOPY VARCHAR2) IS
5600:
5601: l_textline po_online_report_text.text_line%TYPE := NULL;
5602: l_api_name CONSTANT varchar2(40) := 'CHECK_PO';
5603: l_progress VARCHAR2(3);
5604: l_is_complex_po boolean;
5605: l_line_loc_token_value fnd_new_messages.message_text%TYPE;
5653: -- Check 1: Header must have at least one line
5654: -- PO_SUB_HEADER_NO_LINES
5655: -- Message inserted is 'Purchase Document has no lines'
5656: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_HEADER_NO_LINES');
5657: INSERT INTO po_online_report_text_gt(online_report_id,
5658: last_update_login,
5659: last_updated_by,
5660: last_update_date,
5661: created_by,
5711: FND_MESSAGE.SET_NAME('PO','PO_SUB_LINE_NO_SHIP');
5712: FND_MESSAGE.SET_TOKEN('LINE_LOCATION_TYPE', l_line_loc_token_value);
5713:
5714: l_textline := FND_MESSAGE.GET;
5715: INSERT INTO po_online_report_text_gt(online_report_id,
5716: last_update_login,
5717: last_updated_by,
5718: last_update_date,
5719: created_by,
5768:
5769: FND_MESSAGE.SET_NAME('PO', 'PO_SUB_SHIP_NO_DIST');
5770: FND_MESSAGE.SET_TOKEN('LINE_LOCATION_TYPE', l_line_loc_token_value);
5771: l_textline := FND_MESSAGE.GET;
5772: INSERT INTO po_online_report_text_gt(online_report_id,
5773: last_update_login,
5774: last_updated_by,
5775: last_update_date,
5776: created_by,
5847: -- books currency code.
5848: -- PO_SUB_DIST_RATE_NULL
5849:
5850: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_DIST_RATE_NULL');
5851: INSERT INTO po_online_report_text_gt(online_report_id,
5852: last_update_login,
5853: last_updated_by,
5854: last_update_date,
5855: created_by,
5906:
5907: -- Check 7: If using functional currency then rate has to be null.
5908: -- PO_SUB_DIST_RATE_NOT_NULL
5909: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_DIST_RATE_NOT_NULL');
5910: INSERT INTO po_online_report_text_gt(online_report_id,
5911: last_update_login,
5912: last_updated_by,
5913: last_update_date,
5914: created_by,
5975:
5976: IF (l_val_contract_limit = 0) THEN
5977:
5978: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_CONTRACT_AMT_LIMIT');
5979: INSERT INTO po_online_report_text_gt(online_report_id,
5980: last_update_login,
5981: last_updated_by,
5982: last_update_date,
5983: created_by,
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.
6027:
6028: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_REF_UNAPPROVED_CONTRACT');
6029: INSERT INTO po_online_report_text_gt(online_report_id,
6030: last_update_login,
6031: last_updated_by,
6032: last_update_date,
6033: created_by,
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');
6087: INSERT INTO po_online_report_text_gt(online_report_id,
6088: last_update_login,
6089: last_updated_by,
6090: last_update_date,
6091: created_by,
6176: --'Line#
6177: -- is disabled
6178: -- Bug #1630662
6179: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_UOM_CLASS_CONVERSION');
6180: INSERT INTO po_online_report_text_gt(online_report_id,
6181: last_update_login,
6182: last_updated_by,
6183: last_update_date,
6184: created_by,
6287: atlease one approved ASL either at item level or at category level
6288: if there is no item ASL*/
6289:
6290: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ITEM_NOT_APPROVED');
6291: INSERT INTO po_online_report_text_gt(online_report_id,
6292: last_update_login,
6293: last_updated_by,
6294: last_update_date,
6295: created_by,
6384: If supplier is debarred in any of the ASL item/category (Global/Local)
6385: (Suplier/Supplier+site) then the approval of the PO will not be allowed */
6386:
6387: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_ITEM_ASL_DEBARRED');
6388: INSERT INTO po_online_report_text_gt(online_report_id,
6389: last_update_login,
6390: last_updated_by,
6391: last_update_date,
6392: created_by,
6470: -- (If contract is in incomplete status)
6471:
6472: l_textline := FND_MESSAGE.get_string('PO', 'PO_SUB_LINE_CONTRACT_HOLD');
6473:
6474: INSERT INTO po_online_report_text_gt(
6475: online_report_id,
6476: last_update_login,
6477: last_updated_by,
6478: last_update_date,
6526: -- dates of the contracts referenced
6527:
6528: l_textline := FND_MESSAGE.get_string('PO', 'PO_SUB_LINE_CONTRACT_EXP');
6529:
6530: INSERT INTO po_online_report_text_gt(
6531: online_report_id,
6532: last_update_login,
6533: last_updated_by,
6534: last_update_date,
6582: -- Check 17: ATO/CTO Model items not allowed on PO's (Bug 3362369)
6583:
6584: l_textline := FND_MESSAGE.get_string('PO', 'PO_ATO_ITEM_NA');
6585:
6586: INSERT INTO po_online_report_text_gt(
6587: online_report_id,
6588: last_update_login,
6589: last_updated_by,
6590: last_update_date,
6638: 'PO 18: Non Purchasable Item is not allowed');
6639: END IF;
6640: END IF;
6641: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_ALL_NO_ITEM');
6642: INSERT INTO po_online_report_text_gt(online_report_id,
6643: last_update_login,
6644: last_updated_by,
6645: last_update_date,
6646: created_by,
6685: l_api_name||'.' || l_progress,
6686: 'PO 19: Non Purchasable Item is not allowed'); END IF;
6687: END IF;
6688: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_ALL_NO_ITEM');
6689: INSERT INTO po_online_report_text_gt(online_report_id,
6690: last_update_login,
6691: last_updated_by,
6692: last_update_date,
6693: created_by,
6747: -- items (ECO 4503425 for HTML Orders R12)
6748:
6749: l_textline := FND_MESSAGE.get_string('PO', 'PO_PO_PLANNED_ITEM_DATE_REQ');
6750:
6751: INSERT INTO po_online_report_text_gt(
6752: online_report_id,
6753: last_update_login,
6754: last_updated_by,
6755: last_update_date,
6835: l_rowCount(i) := i;
6836: END LOOP;
6837:
6838: FORALL i IN 1..l_line_num.COUNT
6839: INSERT INTO po_online_report_text_gt (online_report_id,
6840: last_update_login,
6841: last_updated_by,
6842: last_update_date,
6843: created_by,
6912: END IF;
6913: END IF;
6914: l_tax_message := fnd_message.get_string('PO','PO_TAX_CALCULATION')||' : ' ;
6915: FORALL i IN 1..po_tax_interface_pvt.G_TAX_ERRORS_TBL.MESSAGE_TEXT.COUNT
6916: INSERT INTO po_online_report_text_gt
6917: (
6918: online_report_id,
6919: last_update_login,
6920: last_updated_by,
6993: IF (l_po_vmi_display_warning = 'Y') THEN
6994:
6995: l_textline := FND_MESSAGE.get_string('PO', 'PO_SUB_VMI_ASL_EXISTS');
6996:
6997: INSERT INTO po_online_report_text_gt(
6998: online_report_id,
6999: last_update_login,
7000: last_updated_by,
7001: last_update_date,
7077: -- Bug 7001748: Added new validation check
7078: -- Check 23: Validate OKE Contract Version, Line Id and Deliverable Id.
7079:
7080: -- Check 23a: Validate Contract Version
7081: INSERT INTO po_online_report_text_gt
7082: (online_report_id,
7083: last_update_login,
7084: last_updated_by,
7085: last_update_date,
7117: --Increment the p_sequence with number of errors reported in last query
7118: p_sequence := p_sequence + SQL%ROWCOUNT;
7119:
7120: -- Check 23b: Validate Contract Line Id
7121: INSERT INTO po_online_report_text_gt
7122: (online_report_id,
7123: last_update_login,
7124: last_updated_by,
7125: last_update_date,
7165: --Increment the p_sequence with number of errors reported in last query
7166: p_sequence := p_sequence + SQL%ROWCOUNT;
7167:
7168: -- Check 23c: Validate Contract Deliverable Id
7169: INSERT INTO po_online_report_text_gt
7170: (online_report_id,
7171: last_update_login,
7172: last_updated_by,
7173: last_update_date,
7223: END IF;
7224: END IF;
7225: -- Check 24: Validate the LCM enabled PO to check whether its invoice match option is set as 'Receipt'.
7226: l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_PO_SHIP_INV_MATCH_NE_R');
7227: INSERT INTO po_online_report_text_gt (online_report_id,
7228: last_update_login,
7229: last_updated_by,
7230: last_update_date,
7231: created_by,
7273:
7274: END IF;
7275: -- Check 25: Validate the LCM enabled PO to check whether its destination type is set as 'Inventory'
7276: l_textline := FND_MESSAGE.GET_STRING('PO','PO_SUB_PO_DIST_DEST_TYPE_NE_IN');
7277: INSERT INTO po_online_report_text_gt (online_report_id,
7278: last_update_login,
7279: last_updated_by,
7280: last_update_date,
7281: created_by,
7364: p_login_id IN NUMBER,
7365: p_sequence IN OUT NOCOPY NUMBER,
7366: x_return_status OUT NOCOPY VARCHAR2) IS
7367:
7368: l_textline po_online_report_text.text_line%TYPE := NULL;
7369: l_api_name CONSTANT varchar2(40) := 'CHECK_PLANNED_PO_BLANKET_PA';
7370: l_progress VARCHAR2(3);
7371:
7372: BEGIN
7385: -- pa should be less than the amount limit.
7386: -- PO_SUB_AGREED_GRT_LIMIT
7387:
7388: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_AGREED_GRT_LIMIT');
7389: INSERT INTO po_online_report_text_gt(online_report_id,
7390: last_update_login,
7391: last_updated_by,
7392: last_update_date,
7393: created_by,
7434: -- blanket pa should be less than the amount limit.
7435: -- PO_SUB_MINREL_GRT_LIMIT
7436:
7437: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_MINREL_GRT_LIMIT');
7438: INSERT INTO po_online_report_text_gt(online_report_id,
7439: last_update_login,
7440: last_updated_by,
7441: last_update_date,
7442: created_by,
7483: -- Releases.
7484: -- PO_SUB_LIMIT_GRT_REL_AMT
7485:
7486: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_LIMIT_GRT_REL_AMT');
7487: INSERT INTO po_online_report_text_gt(online_report_id,
7488: last_update_login,
7489: last_updated_by,
7490: last_update_date,
7491: created_by,
7580:
7581: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SVC_PRICE_LIMIT_LT_PRICE');
7582:
7583:
7584: INSERT into po_online_report_text_gt(online_report_id,
7585: last_update_login,
7586: last_updated_by,
7587: last_update_date,
7588: created_by,
7675: p_check_asl IN BOOLEAN, -- <2757450>
7676: p_sequence IN OUT NOCOPY NUMBER,
7677: x_return_status OUT NOCOPY VARCHAR2) IS
7678:
7679: l_textline po_online_report_text.text_line%TYPE := NULL;
7680: l_api_name CONSTANT varchar2(40) := 'CHECK_BLANKET_AGREEMENT';
7681: l_progress VARCHAR2(3);
7682:
7683: BEGIN
7694: -- Check 1: Blanket Header must have at least one line
7695: -- PO_SUB_HEADER_NO_LINES
7696: -- Message inserted is 'Purchase Document has no lines'
7697: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_HEADER_NO_LINES');
7698: INSERT INTO po_online_report_text_gt(online_report_id,
7699: last_update_login,
7700: last_updated_by,
7701: last_update_date,
7702: created_by,
7783: 'PO 04: Non Purchasable Item is not allowed');
7784: END IF;
7785: END IF;
7786: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_ALL_NO_ITEM');
7787: INSERT INTO po_online_report_text_gt(online_report_id,
7788: last_update_login,
7789: last_updated_by,
7790: last_update_date,
7791: created_by,
7838: 'BPA 06: Checking price breaks effective date range');
7839: END IF;
7840: END IF;
7841:
7842: INSERT INTO po_online_report_text_gt(online_report_id,
7843: last_update_login,
7844: last_updated_by,
7845: last_update_date,
7846: created_by,
7928: END IF;
7929: END IF;
7930:
7931: l_textline := FND_MESSAGE.GET_STRING('PO', PO_MESSAGE_S.POX_EXPIRATION_DATES);
7932: INSERT INTO po_online_report_text_gt(online_report_id,
7933: last_update_login,
7934: last_updated_by,
7935: last_update_date,
7936: created_by,
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 >
8024: l_return_status VARCHAR2(1);
8025:
8026: -- Bug 2818810. Added extra join to alias POHA to return 'Y' only if at least
8027: -- one line references a GA.
8182:
8183: l_api_name CONSTANT varchar2(40) := 'CHECK_STD_CONSIGNED_REF';
8184: l_progress VARCHAR2(3);
8185:
8186: l_textline po_online_report_text.text_line%TYPE := NULL;
8187: l_consigned_from_supplier_flag
8188: po_asl_attributes.consigned_from_supplier_flag%TYPE := NULL;
8189: l_enable_vmi_flag
8190: po_asl_attributes.enable_vmi_flag%TYPE := NULL;
8232:
8233: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUP_CONS_DOC_REF_COEXIST');
8234:
8235: INSERT INTO
8236: po_online_report_text_gt(online_report_id,
8237: last_update_login,
8238: last_updated_by,
8239: last_update_date,
8240: created_by,
8416: -- ASL and the shipment does not match
8417: IF(l_consigned_mismatch_found)
8418: THEN
8419: INSERT INTO
8420: po_online_report_text_gt
8421: (online_report_id,
8422: last_update_login,
8423: last_updated_by,
8424: last_update_date,
8503: p_login_id IN NUMBER,
8504: p_sequence IN OUT NOCOPY NUMBER,
8505: x_return_status OUT NOCOPY VARCHAR2) IS
8506:
8507: l_textline po_online_report_text.text_line%TYPE := NULL;
8508: l_api_name CONSTANT varchar2(40) := 'CHECK_STD_GLOBAL_REF';
8509: l_progress VARCHAR2(3);
8510: l_currency_mismatch BOOLEAN := FALSE; -- Bug 2716769
8511:
8535: --
8536: --Check 1: The GA should be enabled for purchasing in the current OU.
8537: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8538: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_DISABLED');
8539: INSERT into po_online_report_text_gt(online_report_id,
8540: last_update_login,
8541: last_updated_by,
8542: last_update_date,
8543: created_by,
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.
8606:
8607: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_APPROVED');
8608: INSERT into po_online_report_text_gt(online_report_id,
8609: last_update_login,
8610: last_updated_by,
8611: last_update_date,
8612: created_by,
8617: sequence,
8618: text_line,
8619: message_name)
8620: --SQL What: Querying for PO lines that reference GAs that are not approved.
8621: --SQL Why: Add appropriate error message to po_online_report_text_gt
8622: SELECT p_online_report_id,
8623: p_login_id,
8624: p_user_id,
8625: sysdate,
8661:
8662: --Check 3: The GA should not be on hold.
8663: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8664: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_ON_HOLD');
8665: INSERT into po_online_report_text_gt(online_report_id,
8666: last_update_login,
8667: last_updated_by,
8668: last_update_date,
8669: created_by,
8674: sequence,
8675: text_line,
8676: message_name)
8677: --SQL What: Querying for PO lines that reference GAs that are on hold
8678: --SQL Why: Add appropriate error message to po_online_report_text_gt
8679: SELECT p_online_report_id,
8680: p_login_id,
8681: p_user_id,
8682: sysdate,
8720: END IF;
8721:
8722: --Check 4: The vendor on the PO should match the GA.
8723: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_VENDOR_MISMATCH');
8724: INSERT into po_online_report_text_gt(online_report_id,
8725: last_update_login,
8726: last_updated_by,
8727: last_update_date,
8728: created_by,
8734: text_line,
8735: message_name)
8736: --SQL What: Querying for PO lines whose vendor does not
8737: --SQL match the vendor of the referenced GA
8738: --SQL Why: Add appropriate error message to po_online_report_text_gt
8739: SELECT p_online_report_id,
8740: p_login_id,
8741: p_user_id,
8742: sysdate,
8774: -- the GA's enabled org assignments.
8775: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
8776: l_textline :=
8777: FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_VDR_SITE_MISMT');
8778: INSERT into po_online_report_text_gt(online_report_id,
8779: last_update_login,
8780: last_updated_by,
8781: last_update_date,
8782: created_by,
8788: text_line,
8789: message_name)
8790: --SQL What: Querying for PO lines whose vendor_site_id does not
8791: --SQL match a valid vendor_site_id in the GA's org assignments
8792: --SQL Why: Add appropriate error message to po_online_report_text_gt
8793: SELECT p_online_report_id,
8794: p_login_id,
8795: p_user_id,
8796: sysdate,
8839:
8840: --Check 6: The PO creation date should be before Blanket(GA) or
8841: -- corresponding Line expiration date
8842: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_AFTER_GA_DATE');
8843: INSERT into po_online_report_text_gt(online_report_id,
8844: last_update_login,
8845: last_updated_by,
8846: last_update_date,
8847: created_by,
8853: text_line,
8854: message_name)
8855: --SQL What: Querying for PO lines that were not created within
8856: --SQL the effective dates of the referenced GA
8857: --SQL Why: Add appropriate error message to po_online_report_text_gt
8858: SELECT p_online_report_id,
8859: p_login_id,
8860: p_user_id,
8861: sysdate,
8895:
8896: --Check 7: The Need-by-date (or if NULL the PO creation date) should be
8897: -- after the start dates of the GA
8898: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_BEFORE_GA_DATE');
8899: INSERT into po_online_report_text_gt(online_report_id,
8900: last_update_login,
8901: last_updated_by,
8902: last_update_date,
8903: created_by,
8909: text_line,
8910: message_name)
8911: --SQL What: Querying for PO lines that were not created within
8912: --SQL the effective dates of the referenced GA
8913: --SQL Why: Add appropriate error message to po_online_report_text_gt
8914: SELECT p_online_report_id,
8915: p_login_id,
8916: p_user_id,
8917: sysdate,
8948: END IF;
8949:
8950: --Check 8: The currency_code on the PO should match the GA.
8951: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_STD_GA_CURR_MISMATCH');
8952: INSERT into po_online_report_text_gt(online_report_id,
8953: last_update_login,
8954: last_updated_by,
8955: last_update_date,
8956: created_by,
8962: text_line,
8963: message_name)
8964: --SQL What: Querying for PO lines whose currency code does not
8965: --SQL match the currency code of the referenced GA
8966: --SQL Why: Add appropriate error message to po_online_report_text_gt
8967: SELECT p_online_report_id,
8968: p_login_id,
8969: p_user_id,
8970: sysdate,
9121: --raise an error for that line
9122: IF (l_amount_limit(l_curr_doc_line_index) < l_po_amount(l_curr_doc_line_index)) THEN
9123: l_rowcount := l_rowcount +1;
9124: -- Bug 2818810. Use l_curr_doc_line_num to retrieve the line number
9125: INSERT INTO po_online_report_text_gt(online_report_id,
9126: last_update_login,
9127: last_updated_by,
9128: last_update_date,
9129: created_by,
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,
9183: last_updated_by,
9184: last_update_date,
9185: created_by,
9191: text_line,
9192: message_name)
9193: --SQL What: Querying for PO line unit prices that exceed the
9194: --SQL price tolerance on the GA line
9195: --SQL Why: Add appropriate error message to po_online_report_text_gt
9196: SELECT p_online_report_id,
9197: p_login_id,
9198: p_user_id,
9199: sysdate,
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');
9270: INSERT into po_online_report_text_gt(online_report_id,
9271: last_update_login,
9272: last_updated_by,
9273: last_update_date,
9274: created_by,
9280: text_line,
9281: message_name)
9282: --SQL What: Querying for PO shipment totals that fail to meet the
9283: --SQL minimum release amount of the GA line
9284: --SQL Why: Add appropriate error message to po_online_report_text_gt
9285: SELECT p_online_report_id,
9286: p_login_id,
9287: p_user_id,
9288: sysdate,
9395: x_sequence IN OUT NOCOPY NUMBER,
9396: x_return_status OUT NOCOPY VARCHAR2
9397: ) IS
9398:
9399: l_textline PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL;
9400: l_api_name CONSTANT VARCHAR2(40) := 'CHECK_STD_GC_REF';
9401: l_progress VARCHAR2(3);
9402: l_currency_mismatch VARCHAR2(1) := FND_API.G_FALSE;
9403:
9426: --< Shared Proc FPJ > Bug 3301427: Only do this check for new SPO lines
9427:
9428: l_textline := FND_MESSAGE.get_string ('PO', 'PO_SUB_STD_GC_NOT_EN_PUR');
9429:
9430: INSERT INTO po_online_report_text_gt (
9431: online_report_id,
9432: last_update_login,
9433: last_updated_by,
9434: last_update_date,
9499: */
9500:
9501: l_textline := FND_MESSAGE.get_string ('PO', 'PO_SUB_STD_GC_INVALID_SITE');
9502:
9503: INSERT INTO po_online_report_text_gt (
9504: online_report_id,
9505: last_update_login,
9506: last_updated_by,
9507: last_update_date,
9690: l_po_amount(l_curr_doc_line_index))
9691: THEN
9692: l_rowcount := l_rowcount + 1;
9693:
9694: INSERT INTO po_online_report_text_gt (
9695: online_report_id,
9696: last_update_login,
9697: last_updated_by,
9698: last_update_date,
9780: p_login_id IN NUMBER,
9781: p_sequence IN OUT NOCOPY NUMBER,
9782: x_return_status OUT NOCOPY VARCHAR2) IS
9783:
9784: l_textline po_online_report_text.text_line%TYPE := NULL;
9785: l_api_name CONSTANT varchar2(40) := 'CHECK_CONTRACT_AGREEMENT';
9786: l_progress VARCHAR2(3);
9787:
9788: BEGIN
9803: --
9804: --instead of summing up the Line Loc values. The results should be
9805: --equivalent for SPOs.
9806: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_SUB_CONTRACT_AMT_LIMIT');
9807: INSERT INTO po_online_report_text_gt(online_report_id,
9808: last_update_login,
9809: last_updated_by,
9810: last_update_date,
9811: created_by,
9925: l_return_status VARCHAR2(1);
9926: l_cbc_enabled VARCHAR2(1);
9927: l_result NUMBER :=0;
9928:
9929: l_textline po_online_report_text.text_line%TYPE := NULL;
9930: l_api_name CONSTANT varchar2(40) := 'DO_CBC_RELATED_VALIDATIONS';
9931: l_progress VARCHAR2(3);
9932:
9933: BEGIN
9999: --Deleting msg so that we can always use G_Last to get the
10000: --message we want
10001: FND_MSG_PUB.Delete_Msg(p_msg_index => FND_MSG_PUB.G_LAST);
10002: l_progress := '003';
10003: INSERT INTO PO_ONLINE_REPORT_TEXT_GT (online_report_id,
10004: last_update_login,
10005: last_updated_by,
10006: last_update_date,
10007: created_by,
10139: DELETE FROM po_releases_gt;
10140: DELETE FROM po_req_headers_gt;
10141: DELETE FROM po_req_lines_gt;
10142: DELETE FROM po_req_distributions_gt;
10143: DELETE FROM po_online_report_text_gt;
10144:
10145:
10146: PO_CORE_S.get_document_ids(
10147: p_doc_type => p_doc_type
13621: --Pre-reqs:
13622: -- The global temp tables are populated with exactly those rows
13623: -- that should undergo the checks.
13624: --Modifies:
13625: -- PO_ONLINE_REPORT_TEXT_GT
13626: --Locks:
13627: -- None.
13628: --Function:
13629: -- This procedure performs the document checks for an UNRESERVE action.
13629: -- This procedure performs the document checks for an UNRESERVE action.
13630: --Parameters:
13631: --IN:
13632: --p_online_report_id
13633: -- ID used to insert into PO_ONLINE_REPORT_TEXT_GT.
13634: --p_user_id
13635: -- User performing the action.
13636: --p_login_id
13637: -- Last update login_id.
13656:
13657: l_log_head CONSTANT VARCHAR2(100) := g_log_head||'CHECK_UNRESERVE';
13658: l_progress VARCHAR2(3) := '000';
13659:
13660: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE := NULL;
13661: l_ret_sts VARCHAR2(1);
13662: BEGIN
13663:
13664: IF g_debug_stmt THEN
13685: l_progress := '040';
13686:
13687: -- Perform the check.
13688:
13689: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
13690: ( online_report_id
13691: , last_update_login
13692: , last_updated_by
13693: , last_update_date
13771: l_progress := '110';
13772:
13773: -- Perform the check.
13774:
13775: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
13776: ( online_report_id
13777: , last_update_login
13778: , last_updated_by
13779: , last_update_date
13859: l_progress := '210';
13860:
13861: -- Perform the check.
13862:
13863: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
13864: ( online_report_id
13865: , last_update_login
13866: , last_updated_by
13867: , last_update_date
14660: -- The global temp tables for the appropriate doc type
14661: -- have been populated with all of the rows required for the check.
14662: -- This should only be called if encumbrance is on.
14663: --Modifies:
14664: -- PO_ONLINE_REPORT_TEXT_GT
14665: --Locks:
14666: -- None.
14667: --Function:
14668: -- Checks that the GL date is usable (valid for GL and PO).
14674: -- PA
14675: -- PO
14676: -- RELEASE
14677: --p_online_report_id
14678: -- ID used to insert into PO_ONLINE_REPORT_TEXT_GT.
14679: --p_user_id
14680: -- User performing the action.
14681: --p_login_id
14682: -- Last update login_id.
14698:
14699: l_log_head CONSTANT VARCHAR2(100) := g_log_head||'CHECK_GL_DATE';
14700: l_progress VARCHAR2(3) := '000';
14701:
14702: l_msg_name PO_ONLINE_REPORT_TEXT_GT.message_name%TYPE;
14703: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
14704:
14705: l_date_tbl po_tbl_date;
14706: l_line_num_tbl po_tbl_number;
14699: l_log_head CONSTANT VARCHAR2(100) := g_log_head||'CHECK_GL_DATE';
14700: l_progress VARCHAR2(3) := '000';
14701:
14702: l_msg_name PO_ONLINE_REPORT_TEXT_GT.message_name%TYPE;
14703: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
14704:
14705: l_date_tbl po_tbl_date;
14706: l_line_num_tbl po_tbl_number;
14707: l_shipment_num_tbl po_tbl_number;
14896: l_progress := '230';
14897:
14898: -- Report the invalid dates.
14899:
14900: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
14901: ( online_report_id
14902: , last_update_login
14903: , last_updated_by
14904: , last_update_date
15136: --Name: check_vendor_site_ccr_regis
15137: --Pre-reqs:
15138: -- None
15139: --Modifies:
15140: -- po_online_report_text_gt
15141: --Locks:
15142: -- None.
15143: --Function:
15144: -- This procedure is used to perform the submission check related to CCR
15181: l_vendor_id PO_HEADERS.vendor_id%TYPE;
15182: l_vendor_site_id PO_HEADERS.vendor_site_id%TYPE;
15183: l_valid_registration BOOLEAN := FALSE;
15184:
15185: l_text_line PO_ONLINE_REPORT_TEXT.text_line%TYPE := NULL;
15186:
15187: BEGIN
15188:
15189: l_progress := '000';
15244: END IF;
15245: END IF;
15246:
15247: l_text_line := FND_MESSAGE.get_string('PO', 'PO_VENDOR_SITE_CCR_INVALID');
15248: INSERT INTO po_online_report_text_gt
15249: (online_report_id
15250: ,last_updated_by
15251: ,last_update_date
15252: ,created_by
15600: , x_return_status OUT NOCOPY VARCHAR2
15601: )
15602: IS
15603:
15604: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
15605: l_ret_sts VARCHAR2(1);
15606: l_is_complex_po boolean;
15607: l_token_value VARCHAR2(256);
15608:
15635: d_progress := 10;
15636:
15637: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_POH_WITH_RCV_TRX');
15638:
15639: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
15640: ( online_report_id
15641: , last_update_login
15642: , last_updated_by
15643: , last_update_date
15687: d_progress := 20;
15688:
15689: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_REL_WITH_RCV_TRX');
15690:
15691: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
15692: ( online_report_id
15693: , last_update_login
15694: , last_updated_by
15695: , last_update_date
15742: d_progress := 30;
15743:
15744: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_POL_WITH_RCV_TRX');
15745:
15746: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
15747: ( online_report_id
15748: , last_update_login
15749: , last_updated_by
15750: , last_update_date
15811: ELSE
15812: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_RELS_WITH_RCV_TRX');
15813: END IF;
15814:
15815: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
15816: ( online_report_id
15817: , last_update_login
15818: , last_updated_by
15819: , last_update_date
15913: , x_return_status OUT NOCOPY VARCHAR2
15914: )
15915: IS
15916:
15917: l_text_normal_po PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
15918: l_text_complex_po PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
15919:
15920: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_asn_not_fully_received';
15921: d_progress NUMBER;
15914: )
15915: IS
15916:
15917: l_text_normal_po PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
15918: l_text_complex_po PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
15919:
15920: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_asn_not_fully_received';
15921: d_progress NUMBER;
15922:
15938: l_text_normal_po := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_POLL_WITH_ASN');
15939: l_text_complex_po := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_POLL_WITH_PENDING_WCR');
15940:
15941: --
15942: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
15943: ( online_report_id
15944: , last_update_login
15945: , last_updated_by
15946: , last_update_date
16023: , x_return_status OUT NOCOPY VARCHAR2
16024: )
16025: IS
16026:
16027: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16028:
16029: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_qty_rcv_but_not_deliv';
16030: d_progress NUMBER;
16031:
16043: END IF;
16044:
16045: d_progress := 10;
16046:
16047: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16048: ( online_report_id
16049: , last_update_login
16050: , last_updated_by
16051: , last_update_date
16142: , x_return_status OUT NOCOPY VARCHAR2
16143: )
16144: IS
16145:
16146: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16147:
16148: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_amt_rcv_but_not_deliv';
16149: d_progress NUMBER;
16150:
16166: --
16167: -- In query below, changed quantity columns to amount columns and also
16168: -- group by ROWNUM so that it can be used in the select clause
16169:
16170: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16171: ( online_report_id
16172: , last_update_login
16173: , last_updated_by
16174: , last_update_date
16268: , x_return_status OUT NOCOPY VARCHAR2
16269: )
16270: IS
16271:
16272: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16273: l_is_complex_po boolean;
16274: l_token_value VARCHAR2(256);
16275:
16276: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_invalid_acct_flex';
16309: FND_MESSAGE.SET_TOKEN('LINE_LOCATION_TYPE', l_token_value);
16310:
16311: l_textline := FND_MESSAGE.GET;
16312:
16313: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16314: ( online_report_id
16315: , last_update_login
16316: , last_updated_by
16317: , last_update_date
16392: , x_return_status OUT NOCOPY VARCHAR2
16393: )
16394: IS
16395:
16396: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16397:
16398: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_bpa_has_open_release';
16399: d_progress NUMBER;
16400:
16413: d_progress := 10;
16414:
16415: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_POL_PLAN_WITH_OPEN_REL');
16416:
16417: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16418: ( online_report_id
16419: , last_update_login
16420: , last_updated_by
16421: , last_update_date
16489: , x_return_status OUT NOCOPY VARCHAR2
16490: )
16491: IS
16492:
16493: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16494:
16495: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_bpa_has_open_stdref';
16496: d_progress NUMBER;
16497:
16510: d_progress := 10;
16511:
16512: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_GAL_WITH_OPEN_STD_REF');
16513:
16514: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16515: ( online_report_id
16516: , last_update_login
16517: , last_updated_by
16518: , last_update_date
16585: , x_return_status OUT NOCOPY VARCHAR2
16586: )
16587: IS
16588:
16589: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16590:
16591: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_cpa_has_open_stdref';
16592: d_progress NUMBER;
16593:
16606: d_progress := 10;
16607:
16608: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_CGA_WITH_OPEN_STD_REF');
16609:
16610: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16611: ( online_report_id
16612: , last_update_login
16613: , last_updated_by
16614: , last_update_date
16681: , x_return_status OUT NOCOPY VARCHAR2
16682: )
16683: IS
16684:
16685: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
16686:
16687: d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_CHECKS_PVT.check_ppo_has_open_release';
16688: d_progress NUMBER;
16689:
16702: d_progress := 10;
16703:
16704: l_textline := FND_MESSAGE.GET_STRING('PO', 'PO_CAN_POLL_PLAN_WITH_OPEN_REL');
16705:
16706: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
16707: ( online_report_id
16708: , last_update_login
16709: , last_updated_by
16710: , last_update_date
16889: l_rowCount(i) := i;
16890: END LOOP;
16891:
16892: FORALL i IN 1..l_line_num.COUNT
16893: INSERT INTO po_online_report_text_gt(online_report_id,
16894: last_update_login,
16895: last_updated_by,
16896: last_update_date,
16897: created_by,
17055:
17056: --Bug 5440038 and 5517131: Cleared the TODOs for complex work submission checks error messages
17057:
17058: FORALL i IN 1..l_line_num.COUNT
17059: INSERT INTO po_online_report_text_gt(online_report_id,
17060: last_update_login,
17061: last_updated_by,
17062: last_update_date,
17063: created_by,
17161: l_rowCount(i) := i;
17162: END LOOP;
17163:
17164: FORALL i IN 1..l_line_num.COUNT
17165: INSERT INTO po_online_report_text_gt (online_report_id,
17166: last_update_login,
17167: last_updated_by,
17168: last_update_date,
17169: created_by,
17364: , x_return_status OUT NOCOPY VARCHAR2
17365: )
17366: IS
17367:
17368: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
17369: l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
17370: l_calling_sequence VARCHAR2(100);
17371: l_invoice_type VARCHAR2(100) := 'BOTH';
17372: l_is_complex_po boolean;
17418: -- Handle Header final close / Unreserve SPO
17419: --
17420: IF (p_document_level = g_document_level_HEADER) THEN
17421:
17422: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
17423: ( online_report_id
17424: , last_update_login
17425: , last_updated_by
17426: , last_update_date
17453: --
17454: -- Handle Line final close / Unreserve SPO
17455: --
17456: ELSIF (p_document_level = g_document_level_LINE) THEN
17457: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
17458: ( online_report_id
17459: , last_update_login
17460: , last_updated_by
17461: , last_update_date
17488: --
17489: -- Handle Release Header related checks for unvalidated AP Invoices
17490: --
17491: ELSIF (p_document_type = 'RELEASE' AND p_document_level = g_document_level_HEADER) THEN
17492: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
17493: ( online_report_id
17494: , last_update_login
17495: , last_updated_by
17496: , last_update_date
17523: --
17524: -- Handle Shipment/Payitem final close and Unreserve(Both SPO and Release)
17525: --
17526: ELSIF (p_document_level = g_document_level_SHIPMENT) THEN
17527: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
17528: ( online_report_id
17529: , last_update_login
17530: , last_updated_by
17531: , last_update_date
17557: --
17558: -- Handle Distribution Unreserve(for both SPO/Release)
17559: --
17560: ELSIF (p_document_level=g_document_level_DISTRIBUTION AND p_action_requested = g_action_UNRESERVE) THEN
17561: INSERT INTO PO_ONLINE_REPORT_TEXT_GT
17562: ( online_report_id
17563: , last_update_login
17564: , last_updated_by
17565: , last_update_date
17626: x_invoice_type OUT NOCOPY VARCHAR2,
17627: x_calling_sequence OUT NOCOPY VARCHAR2,
17628: x_return_status OUT NOCOPY VARCHAR2) IS
17629:
17630: l_textline PO_ONLINE_REPORT_TEXT_GT.text_line%TYPE;
17631: l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
17632: l_calling_sequence VARCHAR2(100);
17633: l_invoice_type VARCHAR2(100) := 'BOTH';
17634: l_is_complex_po boolean := false;