[Home] [Help]
77: invalid_value)
78: SELECT trx_header_id,
79: arp_standard.fnd_message('AR_INAPI_INVALID_REMIT_ADDR_ID'),
80: remit_to_address_id
81: FROM ar_trx_header_gt gt
82: WHERE gt.remit_to_address_id IS NOT NULL
83: AND NOT EXISTS
84: (SELECT 'X'
85: FROM ar_active_remit_to_addresses_v arta
117: invalid_value)
118: SELECT trx_header_id,
119: arp_standard.fnd_message('AR_INAPI_INVALID_TRX_CLASS'),
120: trx_class
121: FROM ar_trx_header_gt gt
122: WHERE gt.trx_class NOT IN ('INV', 'DM' , 'CM') -- added CM for ER 5869149
123: AND gt.trx_class IS NOT NULL;
124:
125: IF pg_debug = 'Y' THEN
153: invalid_value)
154: SELECT trx_header_id,
155: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_VIA'),
156: ship_via
157: FROM ar_trx_header_gt gt
158: WHERE gt.ship_via IS NOT NULL
159: AND NOT EXISTS
160: (SELECT 'X'
161: FROM org_freight orf
194: invalid_value)
195: SELECT trx_header_id,
196: arp_standard.fnd_message('AR_INAPI_INVALID_FOB'),
197: fob_point
198: FROM ar_trx_header_gt gt
199: WHERE gt.fob_point IS NOT NULL
200: AND NOT EXISTS
201: (SELECT 'X'
202: FROM ar_lookups
236: invalid_value)
237: SELECT trx_header_id,
238: arp_standard.fnd_message('AR_INAPI_INVALID_USSGL_CODE'),
239: default_ussgl_transaction_code
240: FROM ar_trx_header_gt gt
241: WHERE gt.default_ussgl_transaction_code IS NOT NULL
242: AND NOT EXISTS
243: (SELECT 'X'
244: FROM gl_ussgl_transaction_codes gutc
279: invalid_value)
280: SELECT trx_header_id,
281: arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
282: receipt_method_id
283: FROM ar_trx_header_gt gt
284: WHERE gt.receipt_method_id IS NOT NULL
285: AND NOT EXISTS
286: (SELECT 'X'
287: FROM ar_receipt_methods rm
295: invalid_value)
296: SELECT trx_header_id,
297: arp_standard.fnd_message('AR_BOE_OBSOLETE'),
298: receipt_method_id
299: FROM ar_trx_header_gt gt
300: WHERE gt.receipt_method_id IS NOT NULL
301: AND arpt_sql_func_util.check_boe_paymeth(gt.receipt_method_id) = 'Y';
302:
303: /* PAYMENT UPTAKE */
308: invalid_value)
309: SELECT trx_header_id,
310: arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
311: receipt_method_id
312: FROM ar_trx_header_gt gt
313: WHERE gt.payment_trxn_extension_id IS NOT NULL
314: AND EXISTS
315: (SELECT 'X'
316: FROM ar_receipt_methods rm,
325: invalid_value)
326: SELECT trx_header_id,
327: arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
328: receipt_method_id
329: FROM ar_trx_header_gt gt
330: WHERE gt.payment_trxn_extension_id IS NOT NULL
331: AND EXISTS
332: (SELECT 'X'
333: FROM ar_receipt_methods rm,
342: invalid_value)
343: SELECT trx_header_id,
344: arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
345: receipt_method_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 (
353: invalid_value)
354: SELECT trx_header_id,
355: arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
356: receipt_method_id
357: FROM ar_trx_header_gt gt
358: WHERE gt.receipt_method_id IS NULL
359: AND gt.payment_trxn_extension_id IS NOT NULL;
360:
361: IF pg_debug = 'Y' THEN
390: invalid_value)
391: SELECT trx_header_id,
392: arp_standard.fnd_message('AR_INVALID_LEGAL_ENTITY'),
393: legal_entity_id
394: FROM ar_trx_header_gt gt
395: WHERE gt.legal_entity_id IS NOT NULL
396: AND NOT EXISTS (
397: SELECT 'valid LE'
398: FROM XLE_LE_OU_LEDGER_V LE
449: invalid_value)
450: SELECT trx_header_id,
451: arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_CUS_ID'),
452: paying_customer_id
453: FROM ar_trx_header_gt gt
454: WHERE gt.paying_customer_id IS NOT NULL
455: AND NOT EXISTS (
456: SELECT 'X'
457: FROM hz_cust_accounts cust_acct
465: invalid_value)
466: SELECT trx_header_id,
467: arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_CUS_ID'),
468: paying_customer_id
469: FROM ar_trx_header_gt gt
470: WHERE gt.paying_customer_id IS NOT NULL
471: AND NOT EXISTS
472: ( SELECT 'X'
473: FROM hz_cust_accounts cust_acct
525: invalid_value)
526: SELECT trx_header_id,
527: arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_SIT_ID'),
528: paying_site_use_id
529: FROM ar_trx_header_gt gt
530: WHERE gt.paying_site_use_id IS NOT NULL
531: AND NOT EXISTS
532: (SELECT 'X'
533: FROM hz_cust_acct_sites acct_site,
571: invalid_value)
572: SELECT trx_header_id,
573: arp_standard.fnd_message('AR_DAPI_SOLD_CUST_ID_INVALID'),
574: sold_to_customer_id
575: FROM ar_trx_header_gt gt
576: WHERE gt.sold_to_customer_id IS NOT NULL
577: AND NOT EXISTS
578: (SELECT 'X'
579: FROM hz_cust_accounts cust_acct
612: invalid_value)
613: SELECT trx_header_id,
614: arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_NAME'),
615: ship_to_customer_name
616: FROM ar_trx_header_gt gt
617: WHERE gt.ship_to_customer_name IS NOT NULL
618: AND NOT EXISTS
619: (SELECT 'X'
620: FROM hz_cust_accounts cust_acct,
654: invalid_value)
655: SELECT trx_header_id,
656: arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_NUM'),
657: ship_to_account_number
658: FROM ar_trx_header_gt gt
659: WHERE gt.ship_to_account_number IS NOT NULL
660: AND NOT EXISTS
661: (SELECT 'X'
662: FROM hz_cust_accounts cust_acct
694: invalid_value)
695: SELECT trx_header_id,
696: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_NAME'),
697: bill_to_customer_name
698: FROM ar_trx_header_gt gt
699: WHERE gt.bill_to_customer_name IS NOT NULL
700: AND NOT EXISTS
701: (SELECT 'X'
702: FROM hz_cust_accounts cust_acct,
736: invalid_value)
737: SELECT trx_header_id,
738: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_NUM'),
739: bill_to_account_number
740: FROM ar_trx_header_gt gt
741: WHERE gt.bill_to_account_number IS NOT NULL
742: AND NOT EXISTS
743: (SELECT 'X'
744: FROM hz_cust_accounts cust_acct
776: invalid_value)
777: SELECT trx_header_id,
778: arp_standard.fnd_message('AR_INAPI_BAD_BLL_TO_CONTACT_ID'),
779: bill_to_contact_id
780: FROM ar_trx_header_gt gt
781: WHERE gt.bill_to_contact_id IS NOT NULL
782: AND NOT EXISTS
783: (SELECT 'X'
784: FROM hz_cust_account_roles acct_role,
831: invalid_value)
832: SELECT trx_header_id,
833: arp_standard.fnd_message('AR_INAPI_BAD_SHP_TO_CONTACT_ID'),
834: ship_to_contact_id
835: FROM ar_trx_header_gt gt
836: WHERE gt.ship_to_contact_id IS NOT NULL
837: AND NOT EXISTS
838: (SELECT 'X'
839: FROM hz_cust_account_roles acct_role,
886: invalid_value)
887: SELECT trx_header_id,
888: arp_standard.fnd_message('AR_INAPI_INV_XCHNG_RATE_TYPE'),
889: exchange_rate_type
890: FROM ar_trx_header_gt gt
891: WHERE gt.exchange_rate_type IS NOT NULL
892: AND NOT EXISTS
893: (SELECT 'X'
894: FROM gl_daily_conversion_types
916: x_return_status OUT NOCOPY VARCHAR2 ) IS
917:
918: CURSOR header_rows IS
919: SELECT *
920: FROM ar_trx_header_gt gt
921: WHERE gt.doc_sequence_value IS NOT NULL;
922:
923: l_seq_num_profile fnd_profile_option_values.profile_option_value%type;
924:
991: invalid_value)
992: SELECT trx_header_id,
993: arp_standard.fnd_message('AR_TAPI_BFB_BILLING_DATE_REQD'),
994: billing_date
995: FROM ar_trx_header_gt
996: WHERE term_id IS NOT NULL
997: AND billing_date IS NULL
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';
1005: invalid_value)
1006: SELECT trx_header_id,
1007: arp_standard.fnd_message('AR_TAPI_BFB_OPEN_REC'),
1008: term_id
1009: FROM ar_trx_header_gt
1010: WHERE term_id IS NOT NULL
1011: AND cust_trx_type_id IS NOT NULL
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';
1019: invalid_value)
1020: SELECT trx_header_id,
1021: arp_standard.fnd_message('AR_TAPI_BFB_BILLING_DATE_INV'),
1022: billing_date
1023: FROM ar_trx_header_gt
1024: WHERE term_id IS NOT NULL
1025: AND billing_date IS NOT NULL
1026: AND
1027: (
1069: DECODE(b.auto_trx_numbering_flag, 'Y',
1070: arp_standard.fnd_message('AR_INAPI_TRX_NUM_NOT_REQUIRED'),
1071: arp_standard.fnd_message('AR_TW_NULL_TRX_NUMBER')),
1072: trx_number
1073: FROM ar_trx_header_gt gt,
1074: ra_batch_sources b
1075: WHERE b.batch_source_id = gt.batch_source_id
1076: AND ((gt.trx_number IS NULL AND
1077: NVL(b.auto_trx_numbering_flag,'N') = 'N')
1087: invalid_value)
1088: SELECT trx_header_id,
1089: arp_standard.fnd_message('AR_INAPI_BANK_ACC_NOT_REQUIRED'),
1090: customer_bank_account_id
1091: FROM ar_trx_header_gt gt
1092: WHERE gt.customer_bank_account_id IS NOT NULL
1093: AND NOT EXISTS (
1094: SELECT 'X'
1095: FROM ar_receipt_methods rm,
1107: invalid_value)
1108: SELECT trx_header_id,
1109: arp_standard.fnd_message('AR_INAPI_PAYING_SITE_REQUIRED'),
1110: paying_customer_id
1111: FROM ar_trx_header_gt gt
1112: WHERE gt.paying_customer_id IS NOT NULL
1113: AND gt.paying_site_use_id IS NULL;
1114:
1115:
1122: invalid_value)
1123: SELECT trx_header_id,
1124: arp_standard.fnd_message('AR_INAPI_PO_REQUIRED'),
1125: paying_customer_id
1126: FROM ar_trx_header_gt gt
1127: WHERE (gt.purchase_order_revision IS NOT NULL
1128: OR gt.purchase_order_date IS NOT NULL)
1129: AND purchase_order IS NULL;
1130:
1140: lgt.trx_line_id,
1141: arp_standard.fnd_message('AR_INAPI_RULE_INFO_UNNECESSARY'),
1142: NVL(lgt.accounting_rule_id, lgt.accounting_rule_duration)
1143: FROM ar_trx_lines_gt lgt,
1144: ar_trx_header_gt hgt
1145: WHERE (lgt.accounting_rule_id IS NOT NULL
1146: OR lgt.accounting_rule_duration IS NOT NULL)
1147: AND lgt.trx_header_id = hgt.trx_header_id
1148: AND hgt.invoicing_rule_id IS NULL;
1189: invalid_value)
1190: SELECT trx_header_id,
1191: arp_standard.fnd_message('AR_TAPI_EXCHANGE_RATE_REQUIRED'),
1192: gt.trx_currency
1193: FROM ar_trx_header_gt gt
1194: WHERE gt.trx_currency IS NOT NULL
1195: AND ( exchange_rate IS NULL
1196: OR exchange_rate <= 0 )
1197: AND exchange_rate_type = 'User'
1205: invalid_value)
1206: SELECT trx_header_id,
1207: arp_standard.fnd_message('AR_INAPI_RATE_NOT_REQUIRED2'),
1208: exchange_rate
1209: FROM ar_trx_header_gt
1210: WHERE exchange_rate IS NOT NULL
1211: AND exchange_rate_type <> 'User';
1212:
1213: -- Exchange rate cannot be specified for base currency trxns.
1218: invalid_value)
1219: SELECT trx_header_id,
1220: arp_standard.fnd_message('AR_TAPI_EXCHG_INFO_NOT_ALLOWED'),
1221: trx_currency
1222: FROM ar_trx_header_gt
1223: WHERE ( exchange_rate IS NOT NULL
1224: OR exchange_rate_type IS NOT NULL
1225: OR exchange_date IS NOT NULL )
1226: AND trx_currency = p_trx_system_param_rec.base_currency_code;
1270: invalid_value)
1271: SELECT trx_header_id,
1272: arp_standard.fnd_message('AR_TW_NO_LINES'),
1273: null
1274: FROM ar_trx_header_gt gt
1275: WHERE NOT EXISTS
1276: (SELECT 'X'
1277: FROM ar_trx_lines_gt lgt
1278: WHERE lgt.trx_header_id = gt.trx_header_id);
1289: null
1290: FROM ar_trx_lines_gt lgt
1291: WHERE NOT EXISTS
1292: (SELECT 'X'
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 (
1339: invalid_value)
1340: SELECT trx_header_id,
1341: arp_standard.fnd_message('AR_INAPI_HEADER_ID_NOT_UNIQUE'),
1342: null
1343: FROM ar_trx_header_gt hgt
1344: WHERE EXISTS
1345: (SELECT 'X'
1346: FROM ar_trx_header_gt hgt2
1347: WHERE hgt2.trx_header_id = hgt.trx_header_id
1342: null
1343: FROM ar_trx_header_gt hgt
1344: WHERE EXISTS
1345: (SELECT 'X'
1346: FROM ar_trx_header_gt hgt2
1347: WHERE hgt2.trx_header_id = hgt.trx_header_id
1348: GROUP BY hgt2.trx_header_id
1349: HAVING count(*) > 1);
1350:
1385: invalid_value)
1386: SELECT trx_header_id,
1387: arp_standard.fnd_message('AR_TW_INVALID_TRX_NUMBER'),
1388: trx_number
1389: FROM ar_trx_header_gt gt
1390: WHERE gt.trx_number IS NOT NULL
1391: AND gt.batch_source_id IS NOT NULL
1392: AND EXISTS (
1393: SELECT 'X'
1414: AND nvl(batch.allow_duplicate_trx_num_flag,'N') = 'N'
1415: UNION
1416: SELECT 'X'
1417: FROM ra_batch_sources bs,
1418: ar_trx_header_gt ril
1419: WHERE ril.batch_source_id = bs.batch_source_id
1420: AND bs.batch_source_id = gt.batch_source_id
1421: AND ril.trx_number = gt.trx_number
1422: AND ril.customer_trx_id <> NVL(gt.customer_trx_id, -99)
1456: invalid_value)
1457: SELECT trx_header_id,
1458: arp_standard.fnd_message('AR_INAPI_INVALID_BATCH_SOURCE'),
1459: batch_source_id
1460: FROM ar_trx_header_gt gt
1461: WHERE NOT EXISTS (
1462: SELECT 'X'
1463: FROM ra_batch_sources bs
1464: where nvl(gt.trx_date, trunc(sysdate)) between
1502: invalid_value)
1503: SELECT trx_header_id,
1504: arp_standard.fnd_message('AR_INVALID_CURRENCY'),
1505: trx_currency
1506: FROM ar_trx_header_gt gt
1507: WHERE gt.trx_currency IS NOT NULL
1508: AND NOT EXISTS (
1509: SELECT 'X'
1510: FROM fnd_currencies c
1540: invalid_value)
1541: SELECT trx_header_id,
1542: arp_standard.fnd_message('AR_BR_TRX_TYPE_NULL'),
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,
1550: invalid_value)
1551: SELECT trx_header_id,
1552: arp_standard.fnd_message('AR_INVALID_TRX_TYPE'),
1553: cust_trx_type_id
1554: FROM ar_trx_header_gt gt
1555: WHERE gt.cust_trx_type_id IS NOT NULL
1556: AND NOT EXISTS (
1557: SELECT 'X'
1558: FROM ra_cust_trx_types ctt
1591: invalid_value)
1592: SELECT trx_header_id,
1593: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_ID'),
1594: bill_to_customer_id
1595: FROM ar_trx_header_gt gt
1596: WHERE gt.bill_to_customer_id IS NOT NULL
1597: AND NOT EXISTS (
1598: SELECT 'X'
1599: FROM hz_cust_accounts ct
1629: invalid_value)
1630: SELECT trx_header_id,
1631: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_SITE_USE'),
1632: bill_to_site_use_id
1633: FROM ar_trx_header_gt gt
1634: WHERE gt.bill_to_site_use_id IS NOT NULL
1635: AND NOT EXISTS (
1636: SELECT 'X'
1637: FROM hz_cust_site_uses ct
1675: invalid_value)
1676: SELECT trx_header_id,
1677: arp_standard.fnd_message('AR_INAPI_INVALID_BILL_ADDR_ID'),
1678: bill_to_address_id
1679: FROM ar_trx_header_gt gt
1680: WHERE gt.bill_to_address_id IS NOT NULL
1681: AND NOT EXISTS (
1682: SELECT 'X'
1683: FROM HZ_CUST_ACCT_SITES ACCT_SITE,
1730: invalid_value)
1731: SELECT trx_header_id,
1732: arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_ID'),
1733: ship_to_customer_id
1734: FROM ar_trx_header_gt gt
1735: WHERE gt.ship_to_customer_id IS NOT NULL
1736: AND NOT EXISTS (
1737: SELECT 'X'
1738: FROM hz_cust_accounts ct
1770: invalid_value)
1771: SELECT trx_header_id,
1772: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
1773: ship_to_site_use_id
1774: FROM ar_trx_header_gt gt
1775: WHERE gt.ship_to_site_use_id IS NOT NULL
1776: AND gt.ship_to_customer_id IS NOT NULL
1777: AND NOT EXISTS (
1778: SELECT 'X'
1789: invalid_value)
1790: SELECT trx_header_id,
1791: arp_standard.fnd_message('AR_INAPI_NULL_SHIP_TO_SITE'),
1792: ship_to_site_use_id
1793: FROM ar_trx_header_gt gt
1794: WHERE gt.ship_to_site_use_id IS NULL
1795: AND gt.ship_to_customer_id IS NOT NULL;
1796:
1797: IF pg_debug = 'Y'
1822: invalid_value)
1823: SELECT trx_header_id,
1824: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
1825: ship_to_site_use_id
1826: FROM ar_trx_header_gt gt
1827: WHERE gt.ship_to_site_use_id IS NOT NULL
1828: AND NOT EXISTS (
1829: select 'X'
1830: from
1873: -- do it for rows where it is not already populated. Moreover, first we
1874: -- should see if ship_to_address_id is populated then we should derive the
1875: -- ship_to_site_use_id from that.
1876:
1877: UPDATE ar_trx_header_gt gt
1878: SET ship_to_site_use_id = (
1879: SELECT site_use_id
1880: FROM hz_cust_site_uses
1881: WHERE site_use_code = 'SHIP_TO'
1887:
1888: -- Now we will worry about cases where only customer id
1889: -- is populated and we must derive the primary ship to site id.
1890:
1891: UPDATE ar_trx_header_gt gt
1892: SET ship_to_site_use_id = (
1893: SELECT site_use_id
1894: FROM hz_cust_site_uses
1895: WHERE primary_flag = 'Y'
1939: invalid_value)
1940: SELECT trx_header_id,
1941: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
1942: gt.ship_to_site_use_id
1943: FROM ar_trx_header_gt gt
1944: WHERE gt.ship_to_customer_id IS NOT NULL
1945: AND gt.ship_to_site_use_id IS NOT NULL
1946: AND NOT EXISTS (
1947: select 'X'
1994: invalid_value)
1995: SELECT gt.trx_header_id,
1996: arp_standard.fnd_message('AR_INVALID_TERM'),
1997: gt.term_id
1998: FROM ar_trx_header_gt gt,
1999: ra_cust_trx_types ctt
2000: WHERE gt.term_id IS NOT NULL
2001: AND gt.trx_class <> 'CM' -- added for ER 5869149
2002: AND ctt.cust_trx_type_id = gt.cust_trx_type_id -- ER 5869149
2017: invalid_value)
2018: SELECT gt.trx_header_id,
2019: arp_standard.fnd_message('AR_INAPI_TERM_NOT_ALLOWED'),
2020: gt.term_id
2021: FROM ar_trx_header_gt gt,
2022: ra_cust_trx_types ctt
2023: WHERE gt.term_id IS NOT NULL
2024: AND gt.cust_trx_type_id = ctt.cust_trx_type_id
2025: AND ctt.type = 'CM';
2031: invalid_value)
2032: SELECT trx_header_id,
2033: arp_standard.fnd_message('AR_TAPI_BFB_INVALID_TERM'),
2034: term_id
2035: FROM ar_trx_header_gt gt
2036: WHERE gt.term_id IS NOT NULL
2037: AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
2038: AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) = 'N';
2039:
2072: invalid_value)
2073: SELECT trx_header_id,
2074: arp_standard.fnd_message('AR_INVALID_PRIMARY_SALESREP'),
2075: primary_salesrep_id
2076: FROM ar_trx_header_gt gt
2077: WHERE gt.primary_salesrep_id IS NOT NULL
2078: AND NOT EXISTS (
2079: SELECT 'X'
2080: FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2112: invalid_value)
2113: SELECT trx_header_id,
2114: arp_standard.fnd_message(l_message_name),
2115: primary_salesrep_id
2116: FROM ar_trx_header_gt gt
2117: WHERE gt.primary_salesrep_id IS NOT NULL
2118: AND NOT EXISTS (
2119: (SELECT 'X'
2120: FROM ra_salesreps rs
2133: invalid_value)
2134: SELECT trx_header_id,
2135: arp_standard.fnd_message(l_message_name),
2136: primary_salesrep_id
2137: FROM ar_trx_header_gt gt
2138: WHERE gt.primary_salesrep_id IS NULL
2139: AND p_trx_system_param_rec.salesrep_required_flag = 'Y';
2140:
2141: IF pg_debug = 'Y'
2171: invalid_value)
2172: SELECT trx_header_id,
2173: arp_standard.fnd_message('AR_INAPI_INV_RULE_NOT_REQUIRED'),
2174: invoicing_rule_id
2175: FROM ar_trx_header_gt gt
2176: WHERE gt.invoicing_rule_id IS NOT NULL
2177: AND gt.cust_trx_type_id NOT IN (
2178: SELECT tt.cust_trx_type_id
2179: FROM ra_cust_trx_types tt
2186: invalid_value)
2187: SELECT trx_header_id,
2188: arp_standard.fnd_message('AR_INAPI_INVALID_INV_RULE_ID'),
2189: invoicing_rule_id
2190: FROM ar_trx_header_gt gt
2191: WHERE gt.invoicing_rule_id IS NOT NULL
2192: AND gt.invoicing_rule_id not in ( -2, -3);
2193:
2194: IF pg_debug = 'Y'
2224: invalid_value)
2225: SELECT trx_header_id,
2226: arp_standard.fnd_message('AR_INAPI_INVALID_PRINT_OPTION'),
2227: printing_option
2228: FROM ar_trx_header_gt gt
2229: WHERE gt.printing_option IS NOT NULL
2230: AND gt.printing_option NOT IN ( 'PRI', 'NOT');
2231: IF pg_debug = 'Y'
2232: THEN
2256: END IF;
2257:
2258: x_return_status := FND_API.G_RET_STS_SUCCESS;
2259:
2260: update ar_trx_header_gt gt
2261: set gt.printing_pending=decode(gt.PRINTING_OPTION,'PRI','Y','N');
2262:
2263: IF pg_debug = 'Y'
2264: THEN
2309: invalid_value)
2310: SELECT trx_header_id,
2311: arp_standard.fnd_message('AR_INAPI_INV_TAX_EXEMPT_FLAG'),
2312: default_tax_exempt_flag
2313: FROM ar_trx_header_gt gt
2314: WHERE default_tax_exempt_flag IS NOT NULL
2315: AND NOT EXISTS (
2316: select 'X'
2317: from ar_lookups AL1
2353: invalid_value)
2354: SELECT trx_header_id,
2355: arp_standard.fnd_message('AR_INAPI_INVALID_TRX_STATUS'),
2356: status_trx
2357: FROM ar_trx_header_gt
2358: WHERE status_trx IS NOT NULL
2359: AND status_trx not in ( 'OP','CL','PEN','VD');
2360:
2361: IF pg_debug = 'Y'
2390: invalid_value)
2391: SELECT trx_header_id,
2392: arp_standard.fnd_message('AR_INAPI_INVALID_FIN_CHARGE'),
2393: finance_charges
2394: FROM ar_trx_header_gt
2395: WHERE finance_charges IS NOT NULL
2396: AND finance_charges not in ( 'Y','N');
2397:
2398: IF pg_debug = 'Y'
2429: invalid_value)
2430: SELECT trx_header_id,
2431: arp_standard.fnd_message('AR_INAPI_INAVLID_CROSS_REF'),
2432: related_customer_trx_id
2433: FROM ar_trx_header_gt gt
2434: WHERE gt.related_customer_trx_id IS NOT NULL
2435: AND NOT EXISTS (
2436: SELECT 'X'
2437: FROM ra_customer_trx trx, ra_batch_sources bs, ar_lookups look,
2483: /* 5921925 - added post_to_gl logic */
2484: CURSOR cglDate IS
2485: select hdr.trx_header_id, hdr.gl_date, hdr.invoicing_rule_id,
2486: NVL(tt.post_to_gl, 'N') post_to_gl
2487: from ar_trx_header_gt hdr,
2488: ra_cust_trx_types tt
2489: where tt.cust_trx_type_id = hdr.cust_trx_type_id;
2490:
2491: l_period_status gl_period_statuses.closing_status%type DEFAULT 'U';
2618: invalid_value)
2619: SELECT trx_header_id,
2620: arp_standard.fnd_message('AR_INAPI_INVALID_AGREEMENT_ID'),
2621: agreement_id
2622: FROM ar_trx_header_gt gt
2623: WHERE gt.agreement_id IS NOT NULL
2624: AND NOT EXISTS (
2625: SELECT 'X'
2626: from hz_cust_accounts cust_acct,
2689: l_cust_trx_type_id,
2690: l_copy_doc_number_flag
2691: FROM RA_BATCH_SOURCES bs
2692: WHERE batch_source_id = (SELECT gt.batch_source_id
2693: FROM ar_trx_header_gt gt
2694: WHERE rownum =1 );
2695:
2696: IF pg_debug = 'Y'
2697: THEN
2704: -- ORASHID
2705: -- 21-AUG-2003 (END)
2706:
2707:
2708: UPDATE ar_trx_header_gt
2709: SET auto_batch_numbering_flag = l_bs_batch_auto_num_flag,
2710: auto_trx_numbering_flag = l_bs_auto_trx_num_flag,
2711: copy_doc_number_flag = l_copy_doc_number_flag,
2712: cust_trx_type_id = NVL(cust_trx_type_id, l_cust_trx_type_id),
2741: invalid_value)
2742: SELECT gt.trx_header_id,
2743: arp_standard.fnd_message('AR_INAPI_INVALID_BATCH_SOURCE'),
2744: gt.batch_source_id
2745: FROM ar_trx_header_gt gt;
2746: WHEN OTHERS THEN
2747: x_errmsg := 'Error in AR_INVOICE_UTILS.Get_batch_source_details '||sqlerrm;
2748: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2749: RETURN;
2767: l_trx_type ra_cust_trx_types.type%type;
2768:
2769: CURSOR cust_trx_type_c IS
2770: SELECT distinct cust_trx_type_id
2771: FROM ar_trx_header_gt;
2772: BEGIN
2773:
2774: IF pg_debug = 'Y'
2775: THEN
2818:
2819: -- Bug # 3099975
2820: -- ORASHID
2821: -- 21-AUG-2003 (END)
2822: UPDATE ar_trx_header_gt
2823: SET printing_option = nvl(printing_option,l_default_printing_option),
2824: status_trx = NVL(status_trx,l_default_status),
2825: allow_freight_flag = l_allow_freight_flag,
2826: tax_calculation_flag = l_tax_calculation_flag,
2840: invalid_value)
2841: SELECT gt.trx_header_id,
2842: arp_standard.fnd_message('ARTA_INVALID_TRX_TYPE'),
2843: gt.cust_trx_type_id
2844: FROM ar_trx_header_gt gt
2845: WHERE gt.cust_trx_type_id = cust_trx_type_rec.cust_trx_type_id;
2846:
2847: END;
2848: END LOOP;
2873: END IF;
2874:
2875: x_return_status := FND_API.G_RET_STS_SUCCESS;
2876:
2877: UPDATE ar_trx_header_gt gt
2878: SET gt.primary_salesrep_id =
2879: ( SELECT SU.PRIMARY_SALESREP_ID
2880: FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2881: HZ_PARTY_SITES PARTY_SITE,
2922:
2923: -- if bill to customer number is passed then that should be used
2924: -- derive the bill to customer id.
2925:
2926: UPDATE ar_trx_header_gt gt
2927: SET gt.bill_to_customer_id =
2928: (SELECT cust_acct.cust_account_id
2929: FROM hz_cust_accounts cust_acct
2930: WHERE cust_acct.account_number = gt.bill_to_account_number
2937:
2938: -- if ship to customer number is passed then that should be used
2939: -- derive the bill to customer id.
2940:
2941: UPDATE ar_trx_header_gt gt
2942: SET gt.bill_to_customer_id =
2943: (SELECT cust_acct.cust_account_id
2944: FROM hz_cust_accounts cust_acct
2945: WHERE cust_acct.account_number = gt.ship_to_account_number
2952:
2953: -- if ship to customer id is passed then that should be used
2954: -- derive the bill to customer id.
2955:
2956: UPDATE ar_trx_header_gt gt
2957: SET gt.bill_to_customer_id = gt.ship_to_customer_id
2958: WHERE gt.bill_to_customer_id IS NULL
2959: AND gt.ship_to_customer_id IS NOT NULL;
2960:
2961:
2962: -- for the remaining rows bill to customer name should be used
2963: -- derive the bill to customer id.
2964:
2965: UPDATE ar_trx_header_gt gt
2966: SET gt.bill_to_customer_id =
2967: (SELECT cust_acct.cust_account_id
2968: FROM hz_cust_accounts cust_acct,
2969: hz_parties party
3006: -- do it for rows where it is not already populated. Moreover, first we
3007: -- should see if bill_to_address_id is populated then we should derive the
3008: -- bill_to_site_use_id from that.
3009:
3010: UPDATE ar_trx_header_gt gt
3011: SET bill_to_site_use_id = (
3012: SELECT site_use_id
3013: FROM hz_cust_site_uses
3014: WHERE site_use_code = 'BILL_TO'
3020:
3021: -- Now we will worry about cases where only customer id
3022: -- is populated and we must derive the primary ship to site id.
3023:
3024: UPDATE ar_trx_header_gt gt
3025: SET bill_to_site_use_id = (
3026: SELECT site_use_id
3027: FROM hz_cust_site_uses
3028: WHERE primary_flag = 'Y'
3052:
3053: CURSOR cSiteUSe IS
3054: select sgt.bill_to_site_use_id, sgt.bill_to_customer_id,
3055: sgt.paying_customer_id
3056: from ar_trx_header_gt sgt
3057: WHERE sgt.bill_to_customer_id = sgt.paying_customer_id;
3058:
3059: BEGIN
3060: IF pg_debug = 'Y' THEN
3061: debug ('AR_INVOICE_UTILS.populate_paying_site_use_id(+)' );
3062: END IF;
3063:
3064: -- first populate paying_customer_id if it is null
3065: UPDATE ar_trx_header_gt
3066: set paying_customer_id = bill_to_customer_id
3067: WHERE paying_customer_id IS NULL;
3068:
3069: FOR cSiteUSeRec IN cSiteUSe
3067: WHERE paying_customer_id IS NULL;
3068:
3069: FOR cSiteUSeRec IN cSiteUSe
3070: LOOP
3071: UPDATE ar_trx_header_gt ugt
3072: set ugt.paying_site_use_id = cSiteUSeRec.bill_to_site_use_id
3073: WHERE ugt.paying_site_use_id IS NULL
3074: AND ugt.paying_customer_id = cSiteUSeRec.paying_customer_id;
3075:
3078:
3079: -- incase paying_customer_id and bill_to_customer_id is
3080: -- different
3081:
3082: UPDATE ar_trx_header_gt gt
3083: SET paying_site_use_id = (
3084: SELECT site_use_id
3085: FROM hz_cust_site_uses
3086: WHERE primary_flag = 'Y'
3112: -- do it for rows where it is not already populated. Moreover, first we
3113: -- should see if bill_to_site_use_id is populated then we should derive the
3114: -- bill_to_address_id from that.
3115:
3116: UPDATE ar_trx_header_gt gt
3117: SET bill_to_address_id = (
3118: SELECT site_use_id
3119: FROM hz_cust_site_uses
3120: WHERE site_use_code = 'BILL_TO'
3126:
3127: -- Now if it is still not populated then you default it from
3128: -- primary bill to.
3129:
3130: UPDATE ar_trx_header_gt gt
3131: SET gt.bill_to_address_id = (
3132: SELECT su.cust_acct_site_id
3133: FROM hz_cust_acct_sites acct_site,
3134: hz_party_sites party_site,
3169: l_site_use_id NUMBER;
3170:
3171: CURSOR HdrGtc IS
3172: SELECT distinct bill_to_site_use_id
3173: FROM ar_trx_header_gt
3174: WHERE bill_to_site_use_id IS NOT NULL
3175: AND remit_to_address_id IS NULL;
3176:
3177: -- Get country, state, and zip code info. for each bill to site
3207: debug ('Country ' || bill_to_site_rec.country);
3208: debug ('Postal code ' || bill_to_site_rec.postal_code);
3209: END IF;
3210:
3211: UPDATE ar_trx_header_gt gt
3212: SET remit_to_address_id = (
3213: SELECT acct_site.cust_acct_site_id
3214: FROM hz_cust_acct_sites acct_site,
3215: hz_party_sites party_site,
3279:
3280: CURSOR bill_to_customer_id_c IS
3281: SELECT distinct bill_to_customer_id,
3282: bill_to_address_id
3283: FROM ar_trx_header_gt
3284: WHERE bill_to_contact_id IS NULL;
3285: l_contact_id HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ROLE_ID%type;
3286: BEGIN
3287: IF pg_debug = 'Y'
3316: debug ('Bill to contact Id '|| l_contact_id);
3317: debug ('Bill to customer Id ' || bill_to_customer_id_rec.bill_to_customer_id);
3318: debug ('Bill to Address Id ' || bill_to_customer_id_rec.bill_to_address_id);
3319: END IF;
3320: UPDATE ar_trx_header_gt
3321: SET bill_to_contact_id = l_contact_id
3322: WHERE bill_to_customer_id = bill_to_customer_id_rec.bill_to_customer_id
3323: AND bill_to_address_id = bill_to_customer_id_rec.bill_to_address_id
3324: AND bill_to_contact_id IS NOT NULL;
3358:
3359: -- if bill to customer number is passed then that should be used
3360: -- derive the bill to customer id.
3361:
3362: UPDATE ar_trx_header_gt gt
3363: SET gt.ship_to_customer_id =
3364: (SELECT cust_acct.cust_account_id
3365: FROM hz_cust_accounts cust_acct
3366: WHERE cust_acct.account_number = gt.ship_to_account_number
3373:
3374: -- for the remaining rows bill to customer name should be used
3375: -- derive the bill to customer id.
3376:
3377: UPDATE ar_trx_header_gt gt
3378: SET gt.ship_to_customer_id =
3379: (SELECT cust_acct.cust_account_id
3380: FROM hz_cust_accounts cust_acct,
3381: hz_parties party
3419: -- do it for rows where it is not already populated. Moreover, first we
3420: -- should see if ship_to_site_use_id is populated then we should derive the
3421: -- ship_to_address_id from that.
3422:
3423: UPDATE ar_trx_header_gt gt
3424: SET ship_to_address_id = (
3425: SELECT site_use_id
3426: FROM hz_cust_site_uses
3427: WHERE site_use_code = 'SHIP_TO'
3434: -- Now we will worry about cases where only customer id
3435: -- is populated and we must derive the primary ship to site id.
3436:
3437:
3438: UPDATE ar_trx_header_gt gt
3439: SET gt.ship_to_address_id = (
3440: SELECT su.cust_acct_site_id
3441: FROM hz_cust_acct_sites acct_site,
3442: hz_party_sites party_site,
3476:
3477: CURSOR ship_to_customer_id_c IS
3478: SELECT distinct ship_to_customer_id,
3479: ship_to_address_id
3480: FROM ar_trx_header_gt
3481: WHERE ship_to_contact_id IS NULL;
3482: l_contact_id HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ROLE_ID%type;
3483: BEGIN
3484: IF pg_debug = 'Y'
3515: debug ('Ship to Contact Id ' || l_contact_id);
3516: debug ('Ship to Customer Id ' || ship_to_customer_id_rec.ship_to_customer_id);
3517: debug ('Ship to Address Id ' || ship_to_customer_id_rec.ship_to_address_id);
3518: END IF;
3519: UPDATE ar_trx_header_gt
3520: SET ship_to_contact_id = l_contact_id
3521: WHERE ship_to_customer_id = ship_to_customer_id_rec.ship_to_customer_id
3522: AND ship_to_address_id = ship_to_customer_id_rec.ship_to_address_id;
3523:
3549: l_trx_date DATE;
3550: l_salesrep_id NUMBER;
3551: CURSOR cSalesTer IS
3552: SELECT /*+ LEADING(gt) */ st.territory_id territory_id, gt.primary_salesrep_id
3553: FROM ra_salesrep_territories st, ar_trx_header_gt gt
3554: WHERE st.salesrep_id = gt.primary_salesrep_id
3555: AND 'A' = NVL(st.status(+), 'A')
3556: AND gt.trx_date BETWEEN NVL(st.start_date_active(+), gt.trx_date )
3557: AND NVL(st.end_date_active(+), gt.trx_date );
3557: AND NVL(st.end_date_active(+), gt.trx_date );
3558:
3559: CURSOR cBillTo IS
3560: SELECT /*+ LEADING(gt) */ hz.territory_id, gt.bill_to_site_use_id
3561: FROM HZ_CUST_SITE_USES hz, ar_trx_header_gt gt
3562: WHERE hz.site_use_id = gt.bill_to_site_use_id;
3563:
3564: CURSOR cShipTo IS
3565: SELECT /*+ LEADING(gt) */ site_uses.territory_id, gt.ship_to_site_use_id,
3564: CURSOR cShipTo IS
3565: SELECT /*+ LEADING(gt) */ site_uses.territory_id, gt.ship_to_site_use_id,
3566: gt.ship_to_customer_id
3567: FROM HZ_CUST_SITE_USES site_uses,
3568: ar_trx_header_gt gt
3569: WHERE site_uses.SITE_USE_CODE = 'SHIP_TO'
3570: AND site_uses.site_use_id = gt.ship_to_site_use_id
3571: AND site_uses.primary_flag = 'Y';
3572:
3587: IF p_trx_system_param_rec.default_territory = 'BILL'
3588: THEN
3589: FOR cBillToRec IN cBillTo
3590: LOOP
3591: UPDATE ar_trx_header_gt
3592: SET territory_id = cBillToRec.territory_id
3593: WHERE bill_to_site_use_id = cBillToRec.bill_to_site_use_id;
3594: END LOOP;
3595: ELSIF p_trx_system_param_rec.default_territory = 'SHIP'
3595: ELSIF p_trx_system_param_rec.default_territory = 'SHIP'
3596: THEN
3597: FOR cShipToRec IN cShipTo
3598: LOOP
3599: UPDATE ar_trx_header_gt
3600: SET territory_id = cShipToRec.territory_id
3601: WHERE ship_to_site_use_id = cShipToRec.ship_to_site_use_id
3602: AND ship_to_customer_id = cShipToRec.ship_to_customer_id;
3603: END LOOP;
3604: ELSIF p_trx_system_param_rec.default_territory = 'SALES'
3605: THEN
3606: FOR cSalesTerRec IN cSalesTer
3607: LOOP
3608: UPDATE ar_trx_header_gt
3609: SET territory_id = cSalesTerRec.territory_id
3610: WHERE primary_salesrep_id = cSalesTerRec.primary_salesrep_id;
3611: END LOOP;
3612: END IF;
3634: debug ('AR_INVOICE_UTILS.populate_legal_entity(+)' );
3635: END IF;
3636:
3637: /* single update to default value */
3638: UPDATE ar_trx_header_gt
3639: SET legal_entity_id = arp_legal_entity_util.get_default_le(
3640: sold_to_customer_id,
3641: bill_to_customer_id,
3642: cust_trx_type_id,
3672: END IF;
3673:
3674: x_return_status := FND_API.G_RET_STS_SUCCESS;
3675:
3676: UPDATE ar_trx_header_gt gt
3677: SET bill_to_site_use_id = (select site_use_id
3678: from hz_cust_site_uses
3679: where primary_flag = 'Y'
3680: and site_use_code = 'BILL_TO'
3688: debug ('Getting term_id(+)' );
3689: END IF;
3690:
3691: -- Get term_id for NON-BFB enabled customers
3692: UPDATE ar_trx_header_gt gt
3693: SET term_id = (SELECT tl.term_id
3694: FROM ra_terms t_su,
3695: ra_terms t_cp1,
3696: ra_terms t_cp2,
3726: AND gt.trx_class in ('INV','DM') -- added for ER 5869149
3727: AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) = 'N';
3728:
3729: -- R12:BFB : get term_id for BFB-enabled customers
3730: UPDATE ar_trx_header_gt gt
3731: SET term_id = ar_bfb_utils_pvt.get_default_term(
3732: gt.cust_trx_type_id,
3733: gt.trx_date,
3734: gt.org_id,
3743: debug ('Getting term_id(-)' );
3744: debug ('Getting billing_date(+)');
3745: END IF;
3746:
3747: UPDATE ar_trx_header_gt gt
3748: SET billing_date = ar_bfb_utils_pvt.get_billing_date
3749: (ar_bfb_utils_pvt.get_billing_cycle(gt.term_id),
3750: nvl(gt.trx_date,sysdate))
3751: WHERE gt.term_id IS NOT NULL
3755: AND nvl(ar_bfb_utils_pvt.get_cycle_type
3756: (ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'RECURRING';
3757:
3758: -- override incorrect billing dates
3759: UPDATE ar_trx_header_gt gt
3760: SET billing_date = ar_bfb_utils_pvt.get_billing_date
3761: (ar_bfb_utils_pvt.get_billing_cycle(gt.term_id),
3762: gt.billing_date)
3763: WHERE gt.term_id IS NOT NULL
3779: -- ORASHID
3780: -- 21-AUG-2003 (END)
3781:
3782: -- get due_date for NON-BFB enabled
3783: UPDATE ar_trx_header_gt gt
3784: SET term_due_date = trunc(arpt_sql_func_util.get_First_Due_Date(
3785: gt.term_id, NVL(gt.trx_date,sysdate)))
3786: WHERE gt.term_id IS NOT NULL
3787: AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'N'
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
3795: SET term_due_date = ar_bfb_utils_pvt.get_due_date(gt.billing_date, gt.term_id)
3796: WHERE gt.term_id IS NOT NULL
3797: AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'Y'
3798: AND gt.billing_date IS NOT NULL
3836: invalid_value)
3837: SELECT trx_header_id,
3838: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_ID'),
3839: bill_to_address_id
3840: FROM ar_trx_header_gt gt
3841: WHERE gt.bill_to_customer_id IS NULL;
3842: END IF;
3843: populate_ship_to_site_use_id(
3844: x_errmsg => x_errmsg,
3867: invalid_value)
3868: SELECT trx_header_id,
3869: arp_standard.fnd_message('AR_INAPI_INVALID_BILL_ADDR_ID'),
3870: bill_to_address_id
3871: FROM ar_trx_header_gt gt
3872: WHERE gt.bill_to_address_id IS NULL;
3873: END IF;
3874: IF pg_debug = 'Y'
3875: THEN
4056: SELECT gt.trx_header_id,
4057: gt.trx_line_id,
4058: arp_standard.fnd_message('AR_INAPI_CM_UOM_NOT_ALLOWED'),
4059: gt.uom_code
4060: FROM ar_trx_lines_gt gt, ar_trx_header_gt gt2
4061: WHERE gt.uom_code IS NOT NULL
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
4071: SELECT gt.trx_header_id,
4072: gt.trx_line_id,
4073: arp_standard.fnd_message('AR_INAPI_INVALID_UOM'),
4074: gt.uom_code
4075: FROM ar_trx_lines_gt gt , ar_trx_header_gt gt2
4076: WHERE gt.uom_code IS NOT NULL
4077: AND gt.line_type = 'LINE'
4078: AND gt2.trx_header_id = gt.trx_header_id
4079: AND gt2.trx_class <> 'CM' -- added for ER 5869149
4464: error_message)
4465: SELECT gt.trx_header_id,
4466: arp_standard.fnd_message('AR_INAPI_QTY_NOT_NULL')
4467: FROM ar_trx_lines_gt gt ,
4468: ar_trx_header_gt gt2
4469: WHERE gt.quantity_invoiced IS NULL
4470: AND gt.trx_header_id = gt2.trx_header_id
4471: AND gt2.trx_class <> 'CM' -- added for ER 5869149
4472: AND gt.line_type = 'LINE';
4499: error_message)
4500: SELECT gt.trx_header_id,
4501: arp_standard.fnd_message('AR_INAPI_UNIT_PRICE_NOT_NULL')
4502: FROM ar_trx_lines_gt gt ,
4503: ar_trx_header_gt gt2
4504: WHERE gt.unit_selling_price IS NULL
4505: AND gt.trx_header_id = gt2.trx_header_id
4506: AND gt2.trx_class <> 'CM' -- Added for ER 5869149
4507: AND gt.line_type = 'LINE';
4651:
4652: BEGIN
4653: SELECT nvl(gt.allow_freight_flag, 'N'), gt.cust_trx_type_id
4654: INTO l_allow_freight_flag, l_cust_trx_type_id
4655: FROM ar_trx_header_gt gt
4656: WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
4657:
4658: IF l_allow_freight_flag = 'N'
4659: THEN
4696: ( trx_header_id,
4697: error_message)
4698: SELECT trx_header_id,
4699: arp_standard.fnd_message('AR_TAPI_TOO_MANY_FREIGHT_LINE')
4700: FROM ar_trx_header_gt gt
4701: WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
4702: END IF;
4703: END IF;
4704: END LOOP;
4794: gt2.creation_sign, gt.extended_amount,
4795: gt.revenue_amount, gt.quantity_invoiced,
4796: gt.unit_selling_price
4797: FROM ar_trx_lines_gt gt,
4798: ar_trx_header_gt gt2
4799: WHERE gt.line_type ='LINE'
4800: AND gt2.trx_header_id = gt.trx_header_id
4801: AND gt2.trx_class = 'CM';
4802:
4917: ( trx_header_id,
4918: error_message)
4919: SELECT trx_header_id,
4920: arp_standard.fnd_message('AR_INAPI_DUP_DOC_SEQUENCE')
4921: FROM ar_trx_header_gt gt
4922: WHERE gt.cust_trx_type_id IS NOT NULL
4923: AND gt.doc_sequence_value IS NOT NULL
4924: AND EXISTS (
4925: SELECT 'Y' --already exists
4961: x_return_status OUT NOCOPY VARCHAR2) IS
4962:
4963: CURSOR ctrxHeader IS
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:
5106:
5107: IF l_update_trx
5108: THEN
5109:
5110: UPDATE ar_trx_header_gt
5111: SET doc_sequence_value = l_doc_sequence_value,
5112: doc_sequence_id = l_doc_sequence_id,
5113: trx_number = DECODE(ctrxHeaderRec.copy_doc_number_flag,
5114: 'Y',NVL(to_char(l_doc_sequence_value),
5147: x_return_status OUT NOCOPY VARCHAR2) IS
5148:
5149: CURSOR ctrxHeader IS
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;
5251: o_payment_trxn_extension_id := p_trxn_entity_id ;
5252:
5253: arp_standard.debug('the copied value of trx_entn is ' || o_payment_trxn_extension_id );
5254:
5255: UPDATE ar_trx_header_gt
5256: SET payment_trxn_extension_id = o_payment_trxn_extension_id
5257: WHERE trx_header_id = ctrxHeaderRec.trx_header_id;
5258: END IF;
5259:
5382: x_return_status OUT NOCOPY VARCHAR2) IS
5383:
5384: Cursor cTerritory IS
5385: SELECT territory_id, trx_header_id
5386: FROm ar_trx_header_gt
5387: WHERE territory_id IS NOT NULL;
5388:
5389: BEGIN
5390: IF pg_debug = 'Y'
5486: SELECT line.trx_header_id, line.trx_line_id, hdr.invoicing_rule_id,
5487: line.ACCOUNTING_RULE_ID, line.ACCOUNTING_RULE_DURATION,
5488: line.RULE_START_DATE,line.RULE_END_DATE,line.set_of_books_id,hdr.trx_date,
5489: rr.type, rr.frequency, rr.occurrences
5490: FROM ar_trx_lines_gt line, ar_trx_header_gt hdr,
5491: ra_rules rr
5492: WHERE hdr.invoicing_rule_id IS NOT NULL
5493: AND line.accounting_rule_id IS NOT NULL
5494: AND hdr.trx_header_id = line.trx_header_id
5512: error_message)
5513: SELECT gt.trx_header_id,
5514: gt.trx_line_id,
5515: arp_standard.fnd_message('AR_INAPI_INVALID_RULE_NAME')
5516: FROM ar_trx_lines_gt gt , ar_trx_header_gt gt2
5517: WHERE gt.accounting_rule_id IS NOT NULL
5518: AND gt2.trx_header_id = gt.trx_header_id
5519: AND gt2.trx_class <> 'CM' -- Added for ER 5869149
5520: AND NOT EXISTS (
5530: error_message)
5531: SELECT gt.trx_header_id,
5532: gt.trx_line_id,
5533: arp_standard.fnd_message('AR_INAPI_RULE_NAME_NOT_NULL')
5534: FROM ar_trx_lines_gt gt, ar_trx_header_gt gth
5535: WHERE gt.accounting_rule_id IS NULL
5536: AND gt.trx_header_id = gth.trx_header_id
5537: AND gth.invoicing_rule_id IS NOT NULL
5538: AND gth.trx_class <> 'CM' -- Added for ER 5869149
5546: error_message)
5547: SELECT gt.trx_header_id,
5548: gt.trx_line_id,
5549: arp_standard.fnd_message('AR_INAPI_RULE_NOT_ALLOWED')
5550: FROM ar_trx_lines_gt gt, ar_trx_header_gt gth
5551: WHERE (gt.accounting_rule_id IS NOT NULL
5552: OR gth.invoicing_rule_id IS NOT NULL)
5553: AND gt.trx_header_id = gth.trx_header_id
5554: AND gth.trx_class = 'CM' -- Added for ER 5257046
5872: SELECT trx_header_id, trx_currency,
5873: nvl(exchange_rate_type,
5874: p_trx_profile_rec.default_exchange_rate_type) exchange_rate_type,
5875: trunc(nvl(exchange_date,trx_date)) exchange_date, exchange_rate
5876: FROM ar_trx_header_gt gt
5877: WHERE p_trx_system_parameters_rec.base_currency_code <>
5878: trx_currency
5879: AND nvl(exchange_rate_type,
5880: p_trx_profile_rec.default_exchange_rate_type) <> 'User'
5935: THEN
5936: debug ( ' Exchange Rate ' || l_exchange_rate);
5937: END IF;
5938:
5939: UPDATE ar_trx_header_gt
5940: SET exchange_rate = l_exchange_rate,
5941: exchange_date = cExchangeRateRec.exchange_date,
5942: exchange_rate_type = cExchangeRateRec.exchange_rate_type
5943: WHERE trx_header_id = cExchangeRateRec.trx_header_id;
5958: END LOOP;
5959:
5960: -- Update the exchange_date in case exchange_rate_type is 'User'
5961: -- and no exchange date has been provided.
5962: UPDATE ar_trx_header_gt
5963: SET exchange_date = trunc(trx_date)
5964: WHERE exchange_rate_type = 'User'
5965: AND exchange_date IS NULL;
5966:
5972: invalid_value)
5973: SELECT trx_header_id,
5974: arp_standard.fnd_message('AR_EXCHANGE_RATE_NEEDED'),
5975: gt.trx_currency
5976: FROM ar_trx_header_gt gt
5977: WHERE gt.trx_currency IS NOT NULL
5978: AND ( exchange_rate IS NULL
5979: OR exchange_rate <= 0
5980: OR exchange_date IS NULL)
6001: CURSOR cCustDetails IS
6002: SELECT trx_currency, paying_customer_id,
6003: paying_site_use_id, bill_to_customer_id,
6004: bill_to_site_use_id, trx_date, trx_header_id
6005: FROM ar_trx_header_gt
6006: WHERE receipt_method_id IS NULL
6007: AND payment_trxn_extension_id IS NOT NULL;
6008: l_receipt_method_name ar_receipt_methods.name%type;
6009: l_receipt_method_id ar_receipt_methods.receipt_method_id%type;
6036: );
6037: IF l_creation_method_code = 'AUTOMATIC'
6038: AND l_receipt_method_id IS NOT NULL
6039: THEN
6040: UPDATE ar_trx_header_gt
6041: set receipt_method_id = l_receipt_method_id
6042: WHERE trx_header_id = cCustDetailsRec.trx_header_id;
6043: END IF;
6044:
6932: C.MINIMUM_ACCOUNTABLE_UNIT
6933: )
6934: FROM FND_CURRENCIES C,
6935: ar_trx_LINES_GT L,
6936: ar_trx_header_gt h
6937: WHERE C.CURRENCY_CODE = p_trx_system_parameters_rec.base_currency_code
6938: AND L.trx_LINE_ID = D.trx_LINE_ID
6939: AND L.trx_header_id = h.trx_header_id
6940: )
6996: SELECT d.trx_header_id,
6997: d.trx_line_ID,
6998: arp_standard.fnd_message('AR_INAPI_100_PERCENT'),
6999: sum(d.percent)
7000: FROM ar_trx_dist_gt d, ar_trx_lines_gt L, ar_trx_header_gt h
7001: WHERE d.trx_line_id = l.trx_line_id
7002: AND l.trx_header_id = h.trx_header_id
7003: GROUP BY d.trx_header_id,d.trx_line_ID, ACCOUNT_CLASS
7004: HAVING SUM(d.PERCENT) <> 100;
7045: -- update accounting set flag
7046: UPDATE ar_trx_dist_gt dgt
7047: SET dgt.account_set_flag =
7048: (SELECT DECODE(hgt.invoicing_rule_id,null,'N','Y')
7049: FROM ar_trx_header_gt hgt, ar_trx_lines_gt lgt
7050: WHERE hgt.trx_header_id = lgt.trx_header_id
7051: AND lgt.trx_line_id = dgt.trx_line_id
7052: AND dgt.account_class <> 'REC'
7053: UNION
7051: AND lgt.trx_line_id = dgt.trx_line_id
7052: AND dgt.account_class <> 'REC'
7053: UNION
7054: SELECT DECODE(h.invoicing_rule_id,null,'N','Y')
7055: FROM ar_trx_header_gt h
7056: WHERE h.trx_header_id = dgt.trx_header_id
7057: AND dgt.account_class = 'REC');
7058:
7059: -- Now update the amount
7180: l_min_acc_unit)),
7181: D.ACCTD_AMOUNT),
7182: NULL))
7183: FROM ar_trx_DIST_gt D,
7184: ar_trx_header_gt h
7185: --RA_CUST_TRX_TYPES TYPE,
7186: FND_CURRENCIES C,
7187: GL_SETS_OF_BOOKS G,
7188: --RA_CUSTOMER_TRX PREV_TRX,
7227: /*4673387*/
7228: UPDATE ar_trx_dist_gt
7229: SET gl_date = NULL
7230: WHERE trx_header_id IN (SELECT trx_header_id
7231: FROM ar_trx_header_gt gt,
7232: ra_cust_trx_types ctt
7233: WHERE ctt.cust_trx_type_id = gt.cust_trx_type_id
7234: AND ctt.post_to_gl = 'N');
7235: