[Home] [Help]
70: END IF;
71:
72: x_return_status := FND_API.G_RET_STS_SUCCESS;
73:
74: INSERT INTO ar_trx_errors_gt (
75: trx_header_id,
76: error_message,
77: invalid_value)
78: SELECT trx_header_id,
110: END IF;
111:
112: x_return_status := FND_API.G_RET_STS_SUCCESS;
113:
114: INSERT INTO ar_trx_errors_gt (
115: trx_header_id,
116: error_message,
117: invalid_value)
118: SELECT trx_header_id,
146: END IF;
147:
148: x_return_status := FND_API.G_RET_STS_SUCCESS;
149:
150: INSERT INTO ar_trx_errors_gt (
151: trx_header_id,
152: error_message,
153: invalid_value)
154: SELECT trx_header_id,
187: END IF;
188:
189: x_return_status := FND_API.G_RET_STS_SUCCESS;
190:
191: INSERT INTO ar_trx_errors_gt (
192: trx_header_id,
193: error_message,
194: invalid_value)
195: SELECT trx_header_id,
229: END IF;
230:
231: x_return_status := FND_API.G_RET_STS_SUCCESS;
232:
233: INSERT INTO ar_trx_errors_gt (
234: trx_header_id,
235: error_message,
236: invalid_value)
237: SELECT trx_header_id,
272: END IF;
273:
274: x_return_status := FND_API.G_RET_STS_SUCCESS;
275:
276: INSERT INTO ar_trx_errors_gt (
277: trx_header_id,
278: error_message,
279: invalid_value)
280: SELECT trx_header_id,
288: WHERE rm.receipt_method_id = gt.receipt_method_id
289: AND gt.trx_date BETWEEN NVL(rm.start_date, gt.trx_date)
290: AND NVL(rm.end_date, gt.trx_date));
291:
292: INSERT INTO ar_trx_errors_gt (
293: trx_header_id,
294: error_message,
295: invalid_value)
296: SELECT trx_header_id,
301: AND arpt_sql_func_util.check_boe_paymeth(gt.receipt_method_id) = 'Y';
302:
303: /* PAYMENT UPTAKE */
304:
305: INSERT INTO ar_trx_errors_gt (
306: trx_header_id,
307: error_message,
308: invalid_value)
309: SELECT trx_header_id,
318: WHERE rm.receipt_method_id = gt.receipt_method_id
319: AND rm.receipt_class_id = rc.receipt_class_id
320: AND rc.creation_method_code = 'MANUAL' );
321:
322: INSERT INTO ar_trx_errors_gt (
323: trx_header_id,
324: error_message,
325: invalid_value)
326: SELECT trx_header_id,
335: WHERE rm.receipt_method_id = gt.receipt_method_id
336: AND iby.trxn_extension_id= gt.payment_trxn_extension_id
337: AND iby.PAYMENT_CHANNEL_CODE <> rm.payment_channel_code );
338:
339: INSERT INTO ar_trx_errors_gt (
340: trx_header_id,
341: error_message,
342: invalid_value)
343: SELECT trx_header_id,
346: FROM ar_trx_header_gt gt
347: WHERE gt.receipt_method_id IS NOT NULL
348: AND gt.payment_trxn_extension_id IS NULL;
349:
350: INSERT INTO ar_trx_errors_gt (
351: trx_header_id,
352: error_message,
353: invalid_value)
354: SELECT trx_header_id,
383: END IF;
384:
385: x_return_status := FND_API.G_RET_STS_SUCCESS;
386:
387: INSERT INTO ar_trx_errors_gt (
388: trx_header_id,
389: error_message,
390: invalid_value)
391: SELECT trx_header_id,
442: x_return_status := FND_API.G_RET_STS_SUCCESS;
443: -- Removed the prospect join as per the bug 3310138
444: IF p_trx_system_param_rec.pay_unrelated_invoices_flag = 'Y'
445: THEN
446: INSERT INTO ar_trx_errors_gt (
447: trx_header_id,
448: error_message,
449: invalid_value)
450: SELECT trx_header_id,
458: WHERE cust_acct.cust_account_id = gt.paying_customer_id
459: AND cust_acct.status = 'A' );
460: ELSIF p_trx_system_param_rec.pay_unrelated_invoices_flag = 'N'
461: THEN
462: INSERT INTO ar_trx_errors_gt (
463: trx_header_id,
464: error_message,
465: invalid_value)
466: SELECT trx_header_id,
518: END IF;
519:
520: x_return_status := FND_API.G_RET_STS_SUCCESS;
521:
522: INSERT INTO ar_trx_errors_gt (
523: trx_header_id,
524: error_message,
525: invalid_value)
526: SELECT trx_header_id,
564: END IF;
565:
566: x_return_status := FND_API.G_RET_STS_SUCCESS;
567:
568: INSERT INTO ar_trx_errors_gt (
569: trx_header_id,
570: error_message,
571: invalid_value)
572: SELECT trx_header_id,
605: END IF;
606:
607: x_return_status := FND_API.G_RET_STS_SUCCESS;
608:
609: INSERT INTO ar_trx_errors_gt (
610: trx_header_id,
611: error_message,
612: invalid_value)
613: SELECT trx_header_id,
647: END IF;
648:
649: x_return_status := FND_API.G_RET_STS_SUCCESS;
650:
651: INSERT INTO ar_trx_errors_gt (
652: trx_header_id,
653: error_message,
654: invalid_value)
655: SELECT trx_header_id,
687: END IF;
688:
689: x_return_status := FND_API.G_RET_STS_SUCCESS;
690:
691: INSERT INTO ar_trx_errors_gt (
692: trx_header_id,
693: error_message,
694: invalid_value)
695: SELECT trx_header_id,
729: END IF;
730:
731: x_return_status := FND_API.G_RET_STS_SUCCESS;
732:
733: INSERT INTO ar_trx_errors_gt (
734: trx_header_id,
735: error_message,
736: invalid_value)
737: SELECT trx_header_id,
769: END IF;
770:
771: x_return_status := FND_API.G_RET_STS_SUCCESS;
772:
773: INSERT INTO ar_trx_errors_gt (
774: trx_header_id,
775: error_message,
776: invalid_value)
777: SELECT trx_header_id,
824: END IF;
825:
826: x_return_status := FND_API.G_RET_STS_SUCCESS;
827:
828: INSERT INTO ar_trx_errors_gt (
829: trx_header_id,
830: error_message,
831: invalid_value)
832: SELECT trx_header_id,
879: END IF;
880:
881: x_return_status := FND_API.G_RET_STS_SUCCESS;
882:
883: INSERT INTO ar_trx_errors_gt (
884: trx_header_id,
885: error_message,
886: invalid_value)
887: SELECT trx_header_id,
961:
962: IF (l_seq_num_profile = 'N')
963: AND SUBSTRB(l_doc_sequence_type,1,1) IN ( 'A', 'G') THEN
964:
965: INSERT INTO ar_trx_errors_gt (
966: trx_header_id,
967: error_message,
968: invalid_value)
969: VALUES
984: debug ('validate_bfb(+)');
985: END IF;
986:
987: -- R12:BFB : billing_date is required if BFB is event-based
988: INSERT INTO ar_trx_errors_gt (
989: trx_header_id,
990: error_message,
991: invalid_value)
992: SELECT trx_header_id,
998: AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
999: AND nvl(ar_bfb_utils_pvt.get_cycle_type (ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'EVENT';
1000:
1001: -- R12:BFB : BFB is not allowed with open Rec = No
1002: INSERT INTO ar_trx_errors_gt (
1003: trx_header_id,
1004: error_message,
1005: invalid_value)
1006: SELECT trx_header_id,
1012: AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
1013: AND ar_bfb_utils_pvt.get_open_rec(cust_trx_type_id) = 'N';
1014:
1015: -- R12:BFB : billing_date validation
1016: INSERT INTO ar_trx_errors_gt (
1017: trx_header_id,
1018: error_message,
1019: invalid_value)
1020: SELECT trx_header_id,
1060: had the opposite setting as the record for the current org.
1061:
1062: REMOVED _ALL from ra_batch_sources in exists clauses */
1063:
1064: INSERT INTO ar_trx_errors_gt (
1065: trx_header_id,
1066: error_message,
1067: invalid_value)
1068: SELECT trx_header_id,
1080:
1081: -- Accept Customer Bank Account ID only when payment method
1082: -- is passed and it is automatic.
1083:
1084: INSERT INTO ar_trx_errors_gt (
1085: trx_header_id,
1086: error_message,
1087: invalid_value)
1088: SELECT trx_header_id,
1100:
1101: -- If paying_site_use_id must be entered when paying_customer_id is
1102: -- entered.
1103:
1104: INSERT INTO ar_trx_errors_gt (
1105: trx_header_id,
1106: error_message,
1107: invalid_value)
1108: SELECT trx_header_id,
1115:
1116: -- Purchase Order Reveision and Date should be accepted only
1117: -- when purchase order is passed.
1118:
1119: INSERT INTO ar_trx_errors_gt (
1120: trx_header_id,
1121: error_message,
1122: invalid_value)
1123: SELECT trx_header_id,
1130:
1131: -- Accounting Rule ID and Duration should be entered only
1132: -- invoicing rule id is populated at header.
1133:
1134: INSERT INTO ar_trx_errors_gt (
1135: trx_header_id,
1136: trx_line_id,
1137: error_message,
1138: invalid_value)
1147: AND lgt.trx_header_id = hgt.trx_header_id
1148: AND hgt.invoicing_rule_id IS NULL;
1149:
1150: -- Link_to_line_id should always point to a line of type LINE
1151: INSERT INTO ar_trx_errors_gt (
1152: trx_header_id,
1153: trx_line_id,
1154: error_message,
1155: invalid_value)
1165: WHERE gt2.trx_line_id = gt.link_to_trx_line_id
1166: AND gt2.line_type = 'LINE');
1167:
1168: -- Sub Lines can not have a line type of 'LINE'
1169: INSERT INTO ar_trx_errors_gt (
1170: trx_header_id,
1171: trx_line_id,
1172: error_message,
1173: invalid_value)
1182:
1183: -- Exchange rate info is required only if exchange rate type
1184: -- is 'User'
1185:
1186: INSERT INTO ar_trx_errors_gt (
1187: trx_header_id,
1188: error_message,
1189: invalid_value)
1190: SELECT trx_header_id,
1198: AND gt.trx_currency <> p_trx_system_param_rec.base_currency_code;
1199:
1200: -- Exchange rate should not be entered for rate type != 'User'.
1201:
1202: INSERT INTO ar_trx_errors_gt (
1203: trx_header_id,
1204: error_message,
1205: invalid_value)
1206: SELECT trx_header_id,
1211: AND exchange_rate_type <> 'User';
1212:
1213: -- Exchange rate cannot be specified for base currency trxns.
1214:
1215: INSERT INTO ar_trx_errors_gt (
1216: trx_header_id,
1217: error_message,
1218: invalid_value)
1219: SELECT trx_header_id,
1226: AND trx_currency = p_trx_system_param_rec.base_currency_code;
1227:
1228: -- amount includes tax flag should be NULL, Y or N
1229:
1230: INSERT INTO ar_trx_errors_gt (
1231: trx_header_id,
1232: trx_line_id,
1233: error_message,
1234: invalid_value)
1263:
1264: x_return_status := FND_API.G_RET_STS_SUCCESS;
1265:
1266: -- Every header level row should have atleast one line level row.
1267: INSERT INTO ar_trx_errors_gt (
1268: trx_header_id,
1269: error_message,
1270: invalid_value)
1271: SELECT trx_header_id,
1277: FROM ar_trx_lines_gt lgt
1278: WHERE lgt.trx_header_id = gt.trx_header_id);
1279:
1280: -- Every line level row should have a corresponding header level row.
1281: INSERT INTO ar_trx_errors_gt (
1282: trx_header_id,
1283: trx_line_id,
1284: error_message,
1285: invalid_value)
1293: FROM ar_trx_header_gt hgt
1294: WHERE hgt.trx_header_id = lgt.trx_header_id);
1295:
1296: -- Line numbers must be unique
1297: INSERT INTO ar_trx_errors_gt (
1298: trx_header_id,
1299: trx_line_id,
1300: error_message,
1301: invalid_value)
1313: GROUP BY lgt2.trx_header_id, lgt2.line_number
1314: HAVING count(*) > 1);
1315:
1316: -- Line IDs must be unique
1317: INSERT INTO ar_trx_errors_gt (
1318: trx_header_id,
1319: trx_line_id,
1320: error_message,
1321: invalid_value)
1332: GROUP BY lgt2.trx_header_id, lgt2.trx_line_id
1333: HAVING count(*) > 1);
1334:
1335: -- Header IDs must be unique
1336: INSERT INTO ar_trx_errors_gt (
1337: trx_header_id,
1338: error_message,
1339: invalid_value)
1340: SELECT trx_header_id,
1378: END IF;
1379:
1380: x_return_status := FND_API.G_RET_STS_SUCCESS;
1381:
1382: INSERT INTO ar_trx_errors_gt (
1383: trx_header_id,
1384: error_message,
1385: invalid_value)
1386: SELECT trx_header_id,
1449:
1450: x_return_status := FND_API.G_RET_STS_SUCCESS;
1451:
1452: BEGIN
1453: INSERT INTO ar_trx_errors_gt (
1454: trx_header_id,
1455: error_message,
1456: invalid_value)
1457: SELECT trx_header_id,
1495: END IF;
1496:
1497: x_return_status := FND_API.G_RET_STS_SUCCESS;
1498:
1499: INSERT INTO ar_trx_errors_gt
1500: ( trx_header_id,
1501: error_message,
1502: invalid_value)
1503: SELECT trx_header_id,
1533: debug ('AR_INVOICE_UTILS.validate_transaction_type(+)' );
1534: END IF;
1535:
1536: x_return_status := FND_API.G_RET_STS_SUCCESS;
1537: INSERT INTO ar_trx_errors_gt (
1538: trx_header_id,
1539: error_message,
1540: invalid_value)
1541: SELECT trx_header_id,
1543: cust_trx_type_id
1544: FROM ar_trx_header_gt gt
1545: WHERE gt.cust_trx_type_id IS NULL;
1546:
1547: INSERT INTO ar_trx_errors_gt (
1548: trx_header_id,
1549: error_message,
1550: invalid_value)
1551: SELECT trx_header_id,
1584: END IF;
1585:
1586: x_return_status := FND_API.G_RET_STS_SUCCESS;
1587:
1588: INSERT INTO ar_trx_errors_gt (
1589: trx_header_id,
1590: error_message,
1591: invalid_value)
1592: SELECT trx_header_id,
1622: debug ('AR_INVOICE_UTILS.validate_bill_to_site_use_id(+)' );
1623: END IF;
1624: x_return_status := FND_API.G_RET_STS_SUCCESS;
1625:
1626: INSERT INTO ar_trx_errors_gt (
1627: trx_header_id,
1628: error_message,
1629: invalid_value)
1630: SELECT trx_header_id,
1668: END IF;
1669:
1670: x_return_status := FND_API.G_RET_STS_SUCCESS;
1671:
1672: INSERT INTO ar_trx_errors_gt (
1673: trx_header_id,
1674: error_message,
1675: invalid_value)
1676: SELECT trx_header_id,
1723: END IF;
1724:
1725: x_return_status := FND_API.G_RET_STS_SUCCESS;
1726:
1727: INSERT INTO ar_trx_errors_gt (
1728: trx_header_id,
1729: error_message,
1730: invalid_value)
1731: SELECT trx_header_id,
1763: END IF;
1764:
1765: x_return_status := FND_API.G_RET_STS_SUCCESS;
1766:
1767: INSERT INTO ar_trx_errors_gt (
1768: trx_header_id,
1769: error_message,
1770: invalid_value)
1771: SELECT trx_header_id,
1782: select cust_acct_site_id from hz_cust_acct_sites
1783: where cust_account_id = gt.ship_to_customer_id)
1784: and site_use_id = gt.ship_to_site_use_id);
1785:
1786: INSERT INTO ar_trx_errors_gt (
1787: trx_header_id,
1788: error_message,
1789: invalid_value)
1790: SELECT trx_header_id,
1815: END IF;
1816:
1817: x_return_status := FND_API.G_RET_STS_SUCCESS;
1818:
1819: INSERT INTO ar_trx_errors_gt (
1820: trx_header_id,
1821: error_message,
1822: invalid_value)
1823: SELECT trx_header_id,
1932: x_return_status => x_return_status);
1933:
1934: x_return_status := FND_API.G_RET_STS_SUCCESS;
1935:
1936: INSERT INTO ar_trx_errors_gt (
1937: trx_header_id,
1938: error_message,
1939: invalid_value)
1940: SELECT trx_header_id,
1987: debug ('AR_INVOICE_UTILS.validate_terms(+)' );
1988: END IF;
1989: x_return_status := FND_API.G_RET_STS_SUCCESS;
1990:
1991: INSERT INTO ar_trx_errors_gt (
1992: trx_header_id,
1993: error_message,
1994: invalid_value)
1995: SELECT gt.trx_header_id,
2010: and t.term_id = gt.term_id );
2011:
2012: -- Term id not allowed for Credit Memo
2013: -- added for ER 5869149
2014: INSERT INTO ar_trx_errors_gt (
2015: trx_header_id,
2016: error_message,
2017: invalid_value)
2018: SELECT gt.trx_header_id,
2024: AND gt.cust_trx_type_id = ctt.cust_trx_type_id
2025: AND ctt.type = 'CM';
2026:
2027: -- do not allow BFB terms to be used by non-BFB customers
2028: INSERT INTO ar_trx_errors_gt (
2029: trx_header_id,
2030: error_message,
2031: invalid_value)
2032: SELECT trx_header_id,
2065:
2066: x_return_status := FND_API.G_RET_STS_SUCCESS;
2067:
2068: /*
2069: INSERT INTO ar_trx_errors_gt (
2070: trx_header_id,
2071: error_message,
2072: invalid_value)
2073: SELECT trx_header_id,
2105: l_message_name := gl_public_sector.get_message_name (
2106: p_message_name => 'AR_INVALID_PRIMARY_SALESREP',
2107: p_app_short_name => 'AR');
2108:
2109: INSERT INTO ar_trx_errors_gt (
2110: trx_header_id,
2111: error_message,
2112: invalid_value)
2113: SELECT trx_header_id,
2126: l_message_name := gl_public_sector.get_message_name (
2127: p_message_name => 'AR_INAPI_SALESREP_REQUIRED',
2128: p_app_short_name => 'AR');
2129:
2130: INSERT INTO ar_trx_errors_gt (
2131: trx_header_id,
2132: error_message,
2133: invalid_value)
2134: SELECT trx_header_id,
2164: x_return_status := FND_API.G_RET_STS_SUCCESS;
2165:
2166: -- in case of debit memo invoice rule id is not required
2167: -- in case of credit memo invoice rule id is not required ER 5869149
2168: INSERT INTO ar_trx_errors_gt (
2169: trx_header_id,
2170: error_message,
2171: invalid_value)
2172: SELECT trx_header_id,
2179: FROM ra_cust_trx_types tt
2180: WHERE tt.cust_trx_type_id = gt.cust_trx_type_id
2181: AND tt.type = 'INV' );
2182:
2183: INSERT INTO ar_trx_errors_gt (
2184: trx_header_id,
2185: error_message,
2186: invalid_value)
2187: SELECT trx_header_id,
2217: END IF;
2218:
2219: x_return_status := FND_API.G_RET_STS_SUCCESS;
2220:
2221: INSERT INTO ar_trx_errors_gt (
2222: trx_header_id,
2223: error_message,
2224: invalid_value)
2225: SELECT trx_header_id,
2302: END IF;
2303: RETURN;
2304: END;
2305:
2306: INSERT INTO ar_trx_errors_gt (
2307: trx_header_id,
2308: error_message,
2309: invalid_value)
2310: SELECT trx_header_id,
2346: END IF;
2347:
2348: x_return_status := FND_API.G_RET_STS_SUCCESS;
2349:
2350: INSERT INTO ar_trx_errors_gt (
2351: trx_header_id,
2352: error_message,
2353: invalid_value)
2354: SELECT trx_header_id,
2383: END IF;
2384:
2385: x_return_status := FND_API.G_RET_STS_SUCCESS;
2386:
2387: INSERT INTO ar_trx_errors_gt (
2388: trx_header_id,
2389: error_message,
2390: invalid_value)
2391: SELECT trx_header_id,
2422: END IF;
2423:
2424: x_return_status := FND_API.G_RET_STS_SUCCESS;
2425:
2426: INSERT INTO ar_trx_errors_gt (
2427: trx_header_id,
2428: error_message,
2429: invalid_value)
2430: SELECT trx_header_id,
2517: AND trunc(cglDateRec.gl_date) between start_date and end_date;
2518:
2519: IF l_period_status NOT IN ( 'O', 'F')
2520: THEN
2521: INSERT INTO ar_trx_errors_gt (
2522: trx_header_id,
2523: error_message,
2524: invalid_value)
2525: values
2533: IF cglDateRec.invoicing_rule_id = -2
2534: THEN
2535: IF l_period_status not in ('O', 'F' , 'U')
2536: THEN
2537: INSERT INTO ar_trx_errors_gt (
2538: trx_header_id,
2539: error_message,
2540: invalid_value)
2541: values
2546: ELSIF cglDateRec.invoicing_rule_id = -3
2547: THEN
2548: IF l_period_status not in ('O', 'F' , 'U', 'N')
2549: THEN
2550: INSERT INTO ar_trx_errors_gt (
2551: trx_header_id,
2552: error_message,
2553: invalid_value)
2554: values
2560: END IF;
2561:
2562: EXCEPTION
2563: WHEN NO_DATA_FOUND THEN
2564: INSERT INTO ar_trx_errors_gt (
2565: trx_header_id,
2566: error_message,
2567: invalid_value)
2568: values
2575: /* post_to_gl = 'N'.. if a gl_date is supplied,
2576: then it's an error */
2577: IF cglDateRec.gl_date IS NOT NULL
2578: THEN
2579: INSERT INTO ar_trx_errors_gt (
2580: trx_header_id,
2581: error_message,
2582: invalid_value)
2583: values
2611: END IF;
2612:
2613: x_return_status := FND_API.G_RET_STS_SUCCESS;
2614:
2615: INSERT INTO ar_trx_errors_gt (
2616: trx_header_id,
2617: error_message,
2618: invalid_value)
2619: SELECT trx_header_id,
2734: END IF;
2735:
2736: EXCEPTION
2737: WHEN NO_DATA_FOUND THEN
2738: INSERT INTO ar_trx_errors_gt (
2739: trx_header_id,
2740: error_message,
2741: invalid_value)
2742: SELECT gt.trx_header_id,
2833: WHERE cust_trx_type_id = cust_trx_type_rec.cust_trx_type_id;
2834:
2835: EXCEPTION
2836: WHEN NO_DATA_FOUND THEN
2837: INSERT INTO ar_trx_errors_gt (
2838: trx_header_id,
2839: error_message,
2840: invalid_value)
2841: SELECT gt.trx_header_id,
3786: WHERE gt.term_id IS NOT NULL
3787: AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'N'
3788: AND NOT EXISTS
3789: (SELECT 'X'
3790: FROM ar_trx_errors_gt errgt
3791: WHERE errgt.trx_header_id = gt.trx_header_id);
3792:
3793: -- get due_date for BFB enabled
3794: UPDATE ar_trx_header_gt gt
3797: AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'Y'
3798: AND gt.billing_date IS NOT NULL
3799: AND NOT EXISTS
3800: (SELECT 'X'
3801: FROM ar_trx_errors_gt errgt
3802: WHERE errgt.trx_header_id = gt.trx_header_id);
3803:
3804: IF pg_debug = 'Y'
3805: THEN
3829: x_return_status => x_return_status );
3830: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3831: RETURN;
3832: ELSE
3833: INSERT INTO ar_trx_errors_gt (
3834: trx_header_id,
3835: error_message,
3836: invalid_value)
3837: SELECT trx_header_id,
3860:
3861: IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3862: RETURN;
3863: ELSE
3864: INSERT INTO ar_trx_errors_gt (
3865: trx_header_id,
3866: error_message,
3867: invalid_value)
3868: SELECT trx_header_id,
4032: x_return_status := FND_API.G_RET_STS_SUCCESS;
4033:
4034: l_so_org_id := to_number(oe_profile.value('SO_ORGANIZATION_ID')); /*Bug 3752043*/
4035:
4036: INSERT INTO ar_trx_errors_gt
4037: ( trx_header_id,
4038: trx_line_id,
4039: error_message,
4040: invalid_value )
4047: AND gt.line_type <> 'LINE';
4048:
4049: -- UOM Code not allowed for CM
4050:
4051: INSERT INTO ar_trx_errors_gt
4052: ( trx_header_id,
4053: trx_line_id,
4054: error_message,
4055: invalid_value )
4062: AND gt.line_type = 'LINE'
4063: AND gt2.trx_header_id = gt.trx_header_id
4064: AND gt2.trx_class = 'CM'; -- added for ER 5869149
4065:
4066: INSERT INTO ar_trx_errors_gt
4067: ( trx_header_id,
4068: trx_line_id,
4069: error_message,
4070: invalid_value )
4147: END IF;
4148:
4149: x_return_status := FND_API.G_RET_STS_SUCCESS;
4150:
4151: INSERT INTO ar_trx_errors_gt
4152: ( trx_header_id,
4153: trx_line_id,
4154: error_message,
4155: invalid_value )
4160: FROM ar_trx_lines_gt gt
4161: WHERE extended_amount IS NULL
4162: AND line_type in ('TAX', 'FREIGHT');
4163:
4164: INSERT INTO ar_trx_errors_gt
4165: ( trx_header_id,
4166: trx_line_id,
4167: error_message,
4168: invalid_value )
4176: OR unit_selling_price IS NOT NULL );
4177:
4178: /* In etax, a manual tax line must have regime, tax, juris,
4179: rate, and status -- no exceptions */
4180: INSERT INTO ar_trx_errors_gt
4181: ( trx_header_id,
4182: trx_line_id,
4183: error_message,
4184: invalid_value )
4238: IF pg_debug = 'Y'
4239: THEN
4240: debug ('Tax use customer exempt Flag ' || l_tax_use_exempt_flag);
4241: END IF;
4242: INSERT INTO ar_trx_errors_gt
4243: ( trx_header_id,
4244: trx_line_id,
4245: error_message,
4246: invalid_value )
4263: and tax_exempt_flag = lookup_code);
4264:
4265: -- now validate the reson code in case the exempt flag = 'E'
4266: -- if exempt flag is E then reason code must be supplied.
4267: INSERT INTO ar_trx_errors_gt
4268: ( trx_header_id,
4269: trx_line_id,
4270: error_message)
4271: SELECT trx_header_id,
4281: tax_exempt_number = null
4282: WHERE tax_exempt_flag in ( 'R', 'S');
4283:
4284: -- Validate the reason code
4285: INSERT INTO ar_trx_errors_gt
4286: ( trx_header_id,
4287: trx_line_id,
4288: error_message,
4289: invalid_value)
4327:
4328: x_return_status := FND_API.G_RET_STS_SUCCESS;
4329:
4330: -- reason code
4331: INSERT INTO ar_trx_errors_gt
4332: ( trx_header_id,
4333: trx_line_id,
4334: error_message,
4335: invalid_value )
4341: WHERE gt.reason_code IS NOT NULL
4342: AND gt.line_type = 'TAX';
4343:
4344:
4345: INSERT INTO ar_trx_errors_gt
4346: ( trx_header_id,
4347: trx_line_id,
4348: error_message,
4349: invalid_value )
4386: END IF;
4387:
4388: x_return_status := FND_API.G_RET_STS_SUCCESS;
4389:
4390: INSERT INTO ar_trx_errors_gt
4391: ( trx_header_id,
4392: error_message)
4393: SELECT trx_header_id,
4394: arp_standard.fnd_message('AR_INAPI_INV_DESC_NULL')
4397: AND gt.inventory_item_id IS NULL
4398: AND gt.memo_line_id IS NULL
4399: AND gt.line_type = 'LINE';
4400:
4401: INSERT INTO ar_trx_errors_gt
4402: ( trx_header_id,
4403: trx_line_id,
4404: error_message)
4405: SELECT trx_header_id,
4458: END IF;
4459:
4460: x_return_status := FND_API.G_RET_STS_SUCCESS;
4461:
4462: INSERT INTO ar_trx_errors_gt
4463: ( trx_header_id,
4464: error_message)
4465: SELECT gt.trx_header_id,
4466: arp_standard.fnd_message('AR_INAPI_QTY_NOT_NULL')
4493: END IF;
4494:
4495: x_return_status := FND_API.G_RET_STS_SUCCESS;
4496:
4497: INSERT INTO ar_trx_errors_gt
4498: ( trx_header_id,
4499: error_message)
4500: SELECT gt.trx_header_id,
4501: arp_standard.fnd_message('AR_INAPI_UNIT_PRICE_NOT_NULL')
4525: debug ('AR_INVOICE_UTILS.validate_line_type (+)' );
4526: END IF;
4527: x_return_status := FND_API.G_RET_STS_SUCCESS;
4528:
4529: INSERT INTO ar_trx_errors_gt
4530: ( trx_header_id,
4531: error_message,
4532: invalid_value )
4533: SELECT trx_header_id,
4557: debug ('AR_INVOICE_UTILS.check_dup_line_number (+)' );
4558: END IF;
4559: x_return_status := FND_API.G_RET_STS_SUCCESS;
4560: -- first check if line number is null or not
4561: INSERT INTO ar_trx_errors_gt
4562: ( trx_header_id,
4563: error_message)
4564: SELECT trx_header_id,
4565: arp_standard.fnd_message('AR_INAPI_LINE_NUM_NOT_NULL')
4566: FROM ar_trx_lines_gt gt
4567: WHERE gt.line_number IS NULL;
4568:
4569: -- check for duplicate line number
4570: /* INSERT INTO ar_trx_errors_gt
4571: ( trx_header_id,
4572: error_message
4573: SELECT trx_header_id,
4574: 'Duplicate Line Number'
4610:
4611: x_return_status := FND_API.G_RET_STS_SUCCESS;
4612:
4613: -- first check whether all lines have line and header id
4614: INSERT INTO ar_trx_errors_gt
4615: ( trx_header_id,
4616: trx_line_id,
4617: error_message)
4618: SELECT trx_header_id,
4623: OR gt.trx_line_id IS NULL;
4624:
4625: -- Now check if the line is of Tax type then
4626: -- link_to link_to_trx_line_id not null
4627: INSERT INTO ar_trx_errors_gt
4628: ( trx_header_id,
4629: trx_line_id,
4630: error_message)
4631: SELECT trx_header_id,
4656: WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
4657:
4658: IF l_allow_freight_flag = 'N'
4659: THEN
4660: INSERT INTO ar_trx_errors_gt
4661: ( trx_header_id,
4662: error_message)
4663: VALUES
4664: ( c_freight_rec.trx_header_id,
4691: END;
4692: IF ( nvl(l_header_freight_count, 0) >= 1 and
4693: nvl(l_line_freight_count, 0) >= 1 )
4694: THEN
4695: INSERT INTO ar_trx_errors_gt
4696: ( trx_header_id,
4697: error_message)
4698: SELECT trx_header_id,
4699: arp_standard.fnd_message('AR_TAPI_TOO_MANY_FREIGHT_LINE')
4724: debug ('AR_INVOICE_UTILS.validate_freight (+)' );
4725: END IF;
4726: x_return_status := FND_API.G_RET_STS_SUCCESS;
4727:
4728: INSERT INTO ar_trx_errors_gt
4729: ( trx_header_id,
4730: trx_line_id,
4731: error_message)
4732: SELECT trx_header_id,
4742: and gt.trx_date <= nvl(trunc(disable_date), gt.trx_date)
4743: and freight_code = gt.ship_via);
4744:
4745: -- validate FOB
4746: INSERT INTO ar_trx_errors_gt
4747: ( trx_header_id,
4748: trx_line_id,
4749: error_message)
4750: SELECT trx_header_id,
4760: and lookup_code = gt.fob_point
4761: and gt.trx_date between start_date_active and nvl(end_date_active, gt.trx_date));
4762:
4763: -- error if extended_amount is null
4764: INSERT INTO ar_trx_errors_gt
4765: ( trx_header_id,
4766: trx_line_id,
4767: error_message)
4768: SELECT trx_header_id,
4845: NULL;
4846: ELSIF ( c_line_rec.creation_sign = 'P' ) THEN
4847: IF ( (NVL( c_line_rec.extended_amount, 0 ) < 0)
4848: OR ( NVL( c_line_rec.revenue_amount, 0 ) < 0 )) THEN
4849: INSERT INTO ar_trx_errors_gt
4850: ( trx_header_id,
4851: trx_line_id,
4852: error_message)
4853: VALUES
4857: END IF;
4858: ELSIF ( c_line_rec.creation_sign = 'N' ) THEN
4859: IF ( (NVL( c_line_rec.extended_amount, 0 ) > 0)
4860: OR ( NVL( c_line_rec.revenue_amount, 0 ) > 0 )) THEN
4861: INSERT INTO ar_trx_errors_gt
4862: ( trx_header_id,
4863: trx_line_id,
4864: error_message)
4865: VALUES
4875: c_line_rec.unit_selling_price IS NOT NULL AND
4876: c_line_rec.extended_amount IS NOT NULL) THEN
4877: IF (c_line_rec.quantity_invoiced*c_line_rec.unit_selling_price <>
4878: c_line_rec.extended_amount) THEN
4879: INSERT INTO ar_trx_errors_gt
4880: ( trx_header_id,
4881: trx_line_id,
4882: error_message)
4883: VALUES
4912: debug ('AR_INVOICE_UTILS.validate_doc_sequence (+)' );
4913: END IF;
4914: x_return_status := FND_API.G_RET_STS_SUCCESS;
4915:
4916: INSERT INTO ar_trx_errors_gt
4917: ( trx_header_id,
4918: error_message)
4919: SELECT trx_header_id,
4920: arp_standard.fnd_message('AR_INAPI_DUP_DOC_SEQUENCE')
4964: SELECT * FROM
4965: ar_trx_header_gt
4966: WHERE trx_header_id NOT IN (
4967: SELECT trx_header_id FROM
4968: ar_trx_errors_gt);
4969:
4970: l_sequence_name FND_DOCUMENT_SEQUENCES.NAME%TYPE;
4971: l_doc_sequence_id ra_customer_trx.doc_sequence_id%type;
4972: l_doc_sequence_value ra_customer_trx.doc_sequence_value%type;
5023:
5024: IF l_sequence_type IS NULL
5025: AND l_seq_num_profile = 'A'
5026: THEN
5027: INSERT into ar_trx_errors_gt (
5028: trx_header_id,
5029: error_message )
5030: VALUES
5031: ( ctrxHeaderRec.trx_header_id,
5051:
5052: l_update_trx := TRUE;
5053: EXCEPTION
5054: WHEN OTHERS THEN
5055: INSERT into ar_trx_errors_gt (
5056: trx_header_id,
5057: error_message,
5058: invalid_value)
5059: VALUES
5150: SELECT * FROM
5151: ar_trx_header_gt
5152: WHERE trx_header_id NOT IN (
5153: SELECT trx_header_id FROM
5154: ar_trx_errors_gt)
5155: AND payment_trxn_extension_id is not null;
5156:
5157: l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
5158: l_cpy_msg_data VARCHAR2(2000);
5265: l_cpy_msg_data := substrb( l_response_rec.Result_Code || ': '||
5266: l_response_rec.Result_Message , 1, 240);
5267:
5268:
5269: INSERT into ar_trx_errors_gt (
5270: trx_header_id,
5271: error_message,
5272: invalid_value)
5273: VALUES
5327: THEN
5328: -- check whether invoice_enable_flag is 'Y'
5329: IF fnd_flex_keyval.column_value(1) <> 'Y'
5330: THEN
5331: INSERT into ar_trx_errors_gt (
5332: trx_header_id,
5333: trx_line_id,
5334: error_message,
5335: invalid_value)
5352: WHERE trx_line_id = cItemFlexRec.trx_line_id
5353: AND description is NULL;
5354: END IF;
5355: ELSE
5356: INSERT into ar_trx_errors_gt (
5357: trx_header_id,
5358: trx_line_id,
5359: error_message,
5360: invalid_value)
5401: structure_number => 101,
5402: combination_id => cTerritoryRec.territory_id)
5403: <> TRUE )
5404: THEN
5405: INSERT into ar_trx_errors_gt (
5406: trx_header_id,
5407: error_message,
5408: invalid_value)
5409: VALUES
5433: END IF;
5434:
5435: x_return_status := FND_API.G_RET_STS_SUCCESS;
5436:
5437: INSERT INTO ar_trx_errors_gt
5438: ( trx_line_id,
5439: trx_header_id,
5440: error_message,
5441: Invalid_value )
5505:
5506: x_return_status := FND_API.G_RET_STS_SUCCESS;
5507:
5508: --PPRR Added PP rule types in the type condition
5509: INSERT INTO ar_trx_errors_gt
5510: ( trx_header_id,
5511: trx_line_id,
5512: error_message)
5513: SELECT gt.trx_header_id,
5523: WHERE rr.type in ('A', 'ACC_DUR','PP_DR_ALL','PP_DR_PP')
5524: and rr.status = 'A'
5525: AND rr.rule_id = gt.accounting_rule_id);
5526:
5527: INSERT INTO ar_trx_errors_gt
5528: ( trx_header_id,
5529: trx_line_id,
5530: error_message)
5531: SELECT gt.trx_header_id,
5539: AND gt.line_type = 'LINE';
5540:
5541: -- Rule name is not allowed for Credit Memos
5542:
5543: INSERT INTO ar_trx_errors_gt
5544: ( trx_header_id,
5545: trx_line_id,
5546: error_message)
5547: SELECT gt.trx_header_id,
5559: -- first check whether accounting rule name is supplied or not
5560: -- in case it is not supplied then log a error
5561: IF ( c_invoicing_rule_rec.accounting_rule_id IS NULL )
5562: THEN
5563: INSERT INTO ar_trx_errors_gt
5564: ( trx_header_id,
5565: trx_line_id,
5566: error_message) VALUES
5567: ( c_invoicing_rule_rec.trx_header_id,
5569: arp_standard.fnd_message('AR_INAPI_RULE_NAME_NOT_NULL') );
5570: --PPRR Accounting rule Validations
5571: ELSIF c_invoicing_rule_rec.type in ('PP_DR_ALL','PP_DR_PP') THEN
5572: IF c_invoicing_rule_rec.RULE_START_DATE IS NULL THEN
5573: INSERT INTO ar_trx_errors_gt
5574: ( trx_header_id,
5575: trx_line_id,
5576: error_message) VALUES
5577: ( c_invoicing_rule_rec.trx_header_id,
5579: arp_standard.fnd_message('AR_RAXTRX_RULE_START_DT_NULL'));
5580: END IF;
5581:
5582: IF c_invoicing_rule_rec.RULE_END_DATE IS NULL THEN
5583: INSERT INTO ar_trx_errors_gt
5584: ( trx_header_id,
5585: trx_line_id,
5586: error_message) VALUES
5587: ( c_invoicing_rule_rec.trx_header_id,
5590: END IF;
5591:
5592: IF c_invoicing_rule_rec.RULE_END_DATE < c_invoicing_rule_rec.RULE_START_DATE
5593: THEN
5594: INSERT INTO ar_trx_errors_gt
5595: ( trx_header_id,
5596: trx_line_id,
5597: error_message) VALUES
5598: ( c_invoicing_rule_rec.trx_header_id,
5627: IF c_invoicing_rule_rec.accounting_rule_duration IS NULL
5628: THEN
5629: IF c_invoicing_rule_rec.type = 'ACC_DUR'
5630: THEN
5631: INSERT INTO ar_trx_errors_gt
5632: ( trx_header_id,
5633: trx_line_id,
5634: error_message) VALUES
5635: ( c_invoicing_rule_rec.trx_header_id,
5731:
5732: /* Bug 5444387 - Removed the check for trx_date to be equal to rule_start_date of each line
5733: as it is unnecessory and not always satisfied */
5734: IF ( l_period_exist <> 1 ) THEN
5735: INSERT INTO ar_trx_errors_gt
5736: ( trx_header_id,
5737: trx_line_id,
5738: error_message) VALUES
5739: ( c_invoicing_rule_rec.trx_header_id,
5772: END IF;
5773:
5774: x_return_status := FND_API.G_RET_STS_SUCCESS;
5775:
5776: INSERT INTO ar_trx_errors_gt (
5777: trx_header_id,
5778: trx_line_id,
5779: error_message,
5780: invalid_value)
5879: AND nvl(exchange_rate_type,
5880: p_trx_profile_rec.default_exchange_rate_type) <> 'User'
5881: AND NOT EXISTS
5882: (SELECT 'X'
5883: FROM ar_trx_errors_gt errgt
5884: WHERE errgt.trx_header_id = gt.trx_header_id
5885: AND errgt.invalid_value = gt.trx_currency);
5886:
5887: BEGIN
5944: EXCEPTION
5945: WHEN OTHERS THEN
5946: -- in case gl_api fails it will insert record
5947: -- into error table
5948: INSERT INTO ar_trx_errors_gt
5949: ( trx_header_id,
5950: error_message,
5951: invalid_value)
5952: VALUES
5965: AND exchange_date IS NULL;
5966:
5967: -- Now validates whether all exchange information is populated
5968: -- in case trxn currecny <> func currency
5969: INSERT INTO ar_trx_errors_gt (
5970: trx_header_id,
5971: error_message,
5972: invalid_value)
5973: SELECT trx_header_id,
6725: key_flex_code => 'GL#',
6726: structure_number => ARP_GLOBAL.chart_of_accounts_id,
6727: combination_id => CccidRec.code_combination_id )
6728: THEN
6729: INSERT INTO ar_trx_errors_gt
6730: ( trx_header_id,
6731: trx_line_id,
6732: trx_dist_id,
6733: error_message,
6772: l_precision := p_trx_system_parameters_rec.precision;
6773: l_min_acc_unit :=
6774: p_trx_system_parameters_rec.MINIMUM_ACCOUNTABLE_UNIT;
6775:
6776: INSERT INTO ar_trx_errors_gt
6777: ( trx_header_id,
6778: trx_line_id,
6779: trx_dist_id,
6780: error_message)
6787: -- the user can pass either amount or percent. If both amount and percent
6788: -- is passed then amount will take precendence.
6789:
6790: --First check whether user passed either amounts or percent.
6791: INSERT INTO ar_trx_errors_gt
6792: ( trx_header_id,
6793: trx_line_id,
6794: trx_dist_id,
6795: error_message)
6806: -- ignored.
6807: -- first check if account _class is 'REC' and if any accounting rule is passed
6808: -- then only percent is allowed. Amount is not allowed.
6809:
6810: INSERT INTO ar_trx_errors_gt
6811: ( trx_header_id,
6812: trx_line_id,
6813: trx_dist_id,
6814: error_message)
6832:
6833:
6834: -- check whether line type and account class matches
6835:
6836: INSERT INTO ar_trx_errors_gt
6837: (trx_header_id,
6838: trx_line_id,
6839: trx_dist_ID,
6840: error_message)
6957: | FREIGHT must equal the amount at the line of line type FREIGHT |
6958: | CHARGES must equal the amount at the line of line type CHARGES |
6959: +----------------------------------------------------------------*/
6960: /* 4652982 - fixed message name */
6961: INSERT INTO ar_trx_errors_gt
6962: ( trx_header_id,
6963: trx_line_id,
6964: error_message,
6965: invalid_value)
6987:
6988: /*-----------------------------------------------------------------+
6989: | Percent should total to 100 for each account class |
6990: +-----------------------------------------------------------------*/
6991: INSERT INTO ar_trx_errors_gt
6992: (trx_header_id,
6993: trx_LINE_ID,
6994: error_message,
6995: invalid_value)
7005:
7006: /*-----------------------------------------------------------------+
7007: | Percent should total to 100 for REC class in case it is passed |
7008: +-----------------------------------------------------------------*/
7009: INSERT INTO ar_trx_errors_gt
7010: (trx_header_id,
7011: error_message,
7012: invalid_value)
7013: SELECT d.trx_header_id,
7299: p_message_name => 'AR_INAPI_INVALID_SALESREP_ID',
7300: p_app_short_name => 'AR');
7301:
7302: -- validate salesrep_id
7303: INSERT INTO ar_trx_errors_gt
7304: (trx_header_id,
7305: trx_LINE_ID,
7306: trx_salescredit_id,
7307: error_MESSAGE,
7322: p_message_name => 'AR_INAPI_NVALID_SALESREP_NUM',
7323: p_app_short_name => 'AR');
7324:
7325: -- validate salesrep number
7326: INSERT INTO ar_trx_errors_gt
7327: (trx_header_id,
7328: trx_LINE_ID,
7329: trx_salescredit_id,
7330: error_MESSAGE,
7351: p_message_name => 'AR_INAPI_INVALID_SCR_TYPE_ID',
7352: p_app_short_name => 'AR');
7353:
7354: -- validate sales credit type ID
7355: INSERT INTO ar_trx_errors_gt
7356: (trx_header_id,
7357: trx_LINE_ID,
7358: trx_salescredit_id,
7359: error_MESSAGE,
7374: p_message_name => 'AR_INAPI_INVLD_SCR_TYPE_NAME',
7375: p_app_short_name => 'AR');
7376:
7377: -- validate sales credit type name
7378: INSERT INTO ar_trx_errors_gt
7379: (trx_header_id,
7380: trx_LINE_ID,
7381: trx_salescredit_id,
7382: error_MESSAGE,
7405: p_message_name => 'AR_INAPI_SCR_AMT_PERCENT_REQ',
7406: p_app_short_name => 'AR');
7407:
7408: -- validate either amount or percent has been passed by user
7409: INSERT INTO ar_trx_errors_gt
7410: (trx_header_id,
7411: trx_LINE_ID,
7412: trx_salescredit_id,
7413: error_MESSAGE)
7426:
7427: /*--------------------------------------------------------------+
7428: | Verify that sales credit assignments are for line type LINE |
7429: +--------------------------------------------------------------*/
7430: INSERT INTO ar_trx_errors_gt
7431: (trx_header_id,
7432: trx_LINE_ID,
7433: trx_salescredit_id,
7434: error_MESSAGE,
7554:
7555: END LOOP;
7556:
7557: -- validate amount precision
7558: INSERT INTO ar_trx_errors_gt
7559: (trx_header_id,
7560: trx_LINE_ID,
7561: trx_salescredit_id,
7562: error_MESSAGE,
7592: l_message_name := gl_public_sector.get_message_name (
7593: p_message_name => 'AR_INAPI_INVALID_PERCENT_SPLIT',
7594: p_app_short_name => 'AR');
7595:
7596: INSERT INTO ar_trx_errors_gt
7597: (trx_header_id,
7598: trx_LINE_ID,
7599: trx_salescredit_id,
7600: error_MESSAGE,
7616:
7617: /*------------------------------------------------------------+
7618: | Identify un-foot sales credit |
7619: +------------------------------------------------------------*/
7620: INSERT INTO ar_trx_errors_gt
7621: (trx_header_id,
7622: trx_line_id,
7623: error_MESSAGE)
7624: SELECT SC.trx_header_id,
7642: p_app_short_name => 'AR');
7643:
7644: -- identify un-foot sales credit for amount
7645: -- Sum of sales credit does not equal to line amount
7646: INSERT INTO ar_trx_errors_gt
7647: (trx_header_id,
7648: trx_line_id,
7649: error_message,
7650: invalid_value)
7683: l_message_name := gl_public_sector.get_message_name (
7684: p_message_name => 'AR_INAPI_ONE_SALESCREDIT_ROW',
7685: p_app_short_name => 'AR');
7686:
7687: INSERT INTO ar_trx_errors_gt
7688: (trx_header_id,
7689: trx_line_id,
7690: error_message)
7691: SELECT lgt.trx_header_id,