[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: AND EXISTS
350: (SELECT 'X'
360: invalid_value)
361: SELECT trx_header_id,
362: arp_standard.fnd_message('AR_INVALID_PAYMENT_METHOD'),
363: receipt_method_id
364: FROM ar_trx_header_gt gt
365: WHERE gt.receipt_method_id IS NULL
366: AND gt.payment_trxn_extension_id IS NOT NULL;
367:
368: IF pg_debug = 'Y' THEN
397: invalid_value)
398: SELECT trx_header_id,
399: arp_standard.fnd_message('AR_INVALID_LEGAL_ENTITY'),
400: legal_entity_id
401: FROM ar_trx_header_gt gt
402: WHERE gt.legal_entity_id IS NOT NULL
403: AND NOT EXISTS (
404: SELECT 'valid LE'
405: FROM XLE_LE_OU_LEDGER_V LE
456: invalid_value)
457: SELECT trx_header_id,
458: arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_CUS_ID'),
459: paying_customer_id
460: FROM ar_trx_header_gt gt
461: WHERE gt.paying_customer_id IS NOT NULL
462: AND NOT EXISTS (
463: SELECT 'X'
464: FROM hz_cust_accounts cust_acct
472: invalid_value)
473: SELECT trx_header_id,
474: arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_CUS_ID'),
475: paying_customer_id
476: FROM ar_trx_header_gt gt
477: WHERE gt.paying_customer_id IS NOT NULL
478: AND NOT EXISTS
479: ( SELECT 'X'
480: FROM hz_cust_accounts cust_acct
532: invalid_value)
533: SELECT trx_header_id,
534: arp_standard.fnd_message('AR_INAPI_INVALID_PAYING_SIT_ID'),
535: paying_site_use_id
536: FROM ar_trx_header_gt gt
537: WHERE gt.paying_site_use_id IS NOT NULL
538: AND NOT EXISTS
539: (SELECT 'X'
540: FROM hz_cust_acct_sites acct_site,
578: invalid_value)
579: SELECT trx_header_id,
580: arp_standard.fnd_message('AR_DAPI_SOLD_CUST_ID_INVALID'),
581: sold_to_customer_id
582: FROM ar_trx_header_gt gt
583: WHERE gt.sold_to_customer_id IS NOT NULL
584: AND NOT EXISTS
585: (SELECT 'X'
586: FROM hz_cust_accounts cust_acct
619: invalid_value)
620: SELECT trx_header_id,
621: arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_NAME'),
622: ship_to_customer_name
623: FROM ar_trx_header_gt gt
624: WHERE gt.ship_to_customer_name IS NOT NULL
625: AND NOT EXISTS
626: (SELECT 'X'
627: FROM hz_cust_accounts cust_acct,
661: invalid_value)
662: SELECT trx_header_id,
663: arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_NUM'),
664: ship_to_account_number
665: FROM ar_trx_header_gt gt
666: WHERE gt.ship_to_account_number IS NOT NULL
667: AND NOT EXISTS
668: (SELECT 'X'
669: FROM hz_cust_accounts cust_acct
701: invalid_value)
702: SELECT trx_header_id,
703: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_NAME'),
704: bill_to_customer_name
705: FROM ar_trx_header_gt gt
706: WHERE gt.bill_to_customer_name IS NOT NULL
707: AND NOT EXISTS
708: (SELECT 'X'
709: FROM hz_cust_accounts cust_acct,
743: invalid_value)
744: SELECT trx_header_id,
745: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_NUM'),
746: bill_to_account_number
747: FROM ar_trx_header_gt gt
748: WHERE gt.bill_to_account_number IS NOT NULL
749: AND NOT EXISTS
750: (SELECT 'X'
751: FROM hz_cust_accounts cust_acct
783: invalid_value)
784: SELECT trx_header_id,
785: arp_standard.fnd_message('AR_INAPI_BAD_BLL_TO_CONTACT_ID'),
786: bill_to_contact_id
787: FROM ar_trx_header_gt gt
788: WHERE gt.bill_to_contact_id IS NOT NULL
789: AND NOT EXISTS
790: (SELECT 'X'
791: FROM hz_cust_account_roles acct_role,
838: invalid_value)
839: SELECT trx_header_id,
840: arp_standard.fnd_message('AR_INAPI_BAD_SHP_TO_CONTACT_ID'),
841: ship_to_contact_id
842: FROM ar_trx_header_gt gt
843: WHERE gt.ship_to_contact_id IS NOT NULL
844: AND NOT EXISTS
845: (SELECT 'X'
846: FROM hz_cust_account_roles acct_role,
893: invalid_value)
894: SELECT trx_header_id,
895: arp_standard.fnd_message('AR_INAPI_INV_XCHNG_RATE_TYPE'),
896: exchange_rate_type
897: FROM ar_trx_header_gt gt
898: WHERE gt.exchange_rate_type IS NOT NULL
899: AND NOT EXISTS
900: (SELECT 'X'
901: FROM gl_daily_conversion_types
923: x_return_status OUT NOCOPY VARCHAR2 ) IS
924:
925: CURSOR header_rows IS
926: SELECT *
927: FROM ar_trx_header_gt gt
928: WHERE gt.doc_sequence_value IS NOT NULL;
929:
930: l_seq_num_profile fnd_profile_option_values.profile_option_value%type;
931:
998: invalid_value)
999: SELECT trx_header_id,
1000: arp_standard.fnd_message('AR_TAPI_BFB_BILLING_DATE_REQD'),
1001: billing_date
1002: FROM ar_trx_header_gt
1003: WHERE term_id IS NOT NULL
1004: AND billing_date IS NULL
1005: AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
1006: AND nvl(ar_bfb_utils_pvt.get_cycle_type (ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'EVENT';
1012: invalid_value)
1013: SELECT trx_header_id,
1014: arp_standard.fnd_message('AR_TAPI_BFB_OPEN_REC'),
1015: term_id
1016: FROM ar_trx_header_gt
1017: WHERE term_id IS NOT NULL
1018: AND cust_trx_type_id IS NOT NULL
1019: AND ar_bfb_utils_pvt.is_payment_term_bfb(term_id) = 'Y'
1020: AND ar_bfb_utils_pvt.get_open_rec(cust_trx_type_id) = 'N';
1026: invalid_value)
1027: SELECT trx_header_id,
1028: arp_standard.fnd_message('AR_TAPI_BFB_BILLING_DATE_INV'),
1029: billing_date
1030: FROM ar_trx_header_gt
1031: WHERE term_id IS NOT NULL
1032: AND billing_date IS NOT NULL
1033: AND
1034: (
1076: DECODE(b.auto_trx_numbering_flag, 'Y',
1077: arp_standard.fnd_message('AR_INAPI_TRX_NUM_NOT_REQUIRED'),
1078: arp_standard.fnd_message('AR_TW_NULL_TRX_NUMBER')),
1079: trx_number
1080: FROM ar_trx_header_gt gt,
1081: ra_batch_sources b
1082: WHERE b.batch_source_id = gt.batch_source_id
1083: AND ((gt.trx_number IS NULL AND
1084: NVL(b.auto_trx_numbering_flag,'N') = 'N')
1094: invalid_value)
1095: SELECT trx_header_id,
1096: arp_standard.fnd_message('AR_INAPI_BANK_ACC_NOT_REQUIRED'),
1097: customer_bank_account_id
1098: FROM ar_trx_header_gt gt
1099: WHERE gt.customer_bank_account_id IS NOT NULL
1100: AND NOT EXISTS (
1101: SELECT 'X'
1102: FROM ar_receipt_methods rm,
1114: invalid_value)
1115: SELECT trx_header_id,
1116: arp_standard.fnd_message('AR_INAPI_PAYING_SITE_REQUIRED'),
1117: paying_customer_id
1118: FROM ar_trx_header_gt gt
1119: WHERE gt.paying_customer_id IS NOT NULL
1120: AND gt.paying_site_use_id IS NULL;
1121:
1122:
1129: invalid_value)
1130: SELECT trx_header_id,
1131: arp_standard.fnd_message('AR_INAPI_PO_REQUIRED'),
1132: paying_customer_id
1133: FROM ar_trx_header_gt gt
1134: WHERE (gt.purchase_order_revision IS NOT NULL
1135: OR gt.purchase_order_date IS NOT NULL)
1136: AND purchase_order IS NULL;
1137:
1147: lgt.trx_line_id,
1148: arp_standard.fnd_message('AR_INAPI_RULE_INFO_UNNECESSARY'),
1149: NVL(lgt.accounting_rule_id, lgt.accounting_rule_duration)
1150: FROM ar_trx_lines_gt lgt,
1151: ar_trx_header_gt hgt
1152: WHERE (lgt.accounting_rule_id IS NOT NULL
1153: OR lgt.accounting_rule_duration IS NOT NULL)
1154: AND lgt.trx_header_id = hgt.trx_header_id
1155: AND hgt.invoicing_rule_id IS NULL;
1196: invalid_value)
1197: SELECT trx_header_id,
1198: arp_standard.fnd_message('AR_TAPI_EXCHANGE_RATE_REQUIRED'),
1199: gt.trx_currency
1200: FROM ar_trx_header_gt gt
1201: WHERE gt.trx_currency IS NOT NULL
1202: AND ( exchange_rate IS NULL
1203: OR exchange_rate <= 0 )
1204: AND exchange_rate_type = 'User'
1212: invalid_value)
1213: SELECT trx_header_id,
1214: arp_standard.fnd_message('AR_INAPI_RATE_NOT_REQUIRED2'),
1215: exchange_rate
1216: FROM ar_trx_header_gt
1217: WHERE exchange_rate IS NOT NULL
1218: AND exchange_rate_type <> 'User';
1219:
1220: -- Exchange rate cannot be specified for base currency trxns.
1225: invalid_value)
1226: SELECT trx_header_id,
1227: arp_standard.fnd_message('AR_TAPI_EXCHG_INFO_NOT_ALLOWED'),
1228: trx_currency
1229: FROM ar_trx_header_gt
1230: WHERE ( exchange_rate IS NOT NULL
1231: OR exchange_rate_type IS NOT NULL
1232: OR exchange_date IS NOT NULL )
1233: AND trx_currency = p_trx_system_param_rec.base_currency_code;
1277: invalid_value)
1278: SELECT trx_header_id,
1279: arp_standard.fnd_message('AR_TW_NO_LINES'),
1280: null
1281: FROM ar_trx_header_gt gt
1282: WHERE NOT EXISTS
1283: (SELECT 'X'
1284: FROM ar_trx_lines_gt lgt
1285: WHERE lgt.trx_header_id = gt.trx_header_id);
1296: null
1297: FROM ar_trx_lines_gt lgt
1298: WHERE NOT EXISTS
1299: (SELECT 'X'
1300: FROM ar_trx_header_gt hgt
1301: WHERE hgt.trx_header_id = lgt.trx_header_id);
1302:
1303: -- Line numbers must be unique
1304: INSERT INTO ar_trx_errors_gt (
1346: invalid_value)
1347: SELECT trx_header_id,
1348: arp_standard.fnd_message('AR_INAPI_HEADER_ID_NOT_UNIQUE'),
1349: null
1350: FROM ar_trx_header_gt hgt
1351: WHERE EXISTS
1352: (SELECT 'X'
1353: FROM ar_trx_header_gt hgt2
1354: WHERE hgt2.trx_header_id = hgt.trx_header_id
1349: null
1350: FROM ar_trx_header_gt hgt
1351: WHERE EXISTS
1352: (SELECT 'X'
1353: FROM ar_trx_header_gt hgt2
1354: WHERE hgt2.trx_header_id = hgt.trx_header_id
1355: GROUP BY hgt2.trx_header_id
1356: HAVING count(*) > 1);
1357:
1392: invalid_value)
1393: SELECT trx_header_id,
1394: arp_standard.fnd_message('AR_TW_INVALID_TRX_NUMBER'),
1395: trx_number
1396: FROM ar_trx_header_gt gt
1397: WHERE gt.trx_number IS NOT NULL
1398: AND gt.batch_source_id IS NOT NULL
1399: AND EXISTS (
1400: SELECT 'X'
1421: AND nvl(batch.allow_duplicate_trx_num_flag,'N') = 'N'
1422: UNION
1423: SELECT 'X'
1424: FROM ra_batch_sources bs,
1425: ar_trx_header_gt ril
1426: WHERE ril.batch_source_id = bs.batch_source_id
1427: AND bs.batch_source_id = gt.batch_source_id
1428: AND ril.trx_number = gt.trx_number
1429: AND ril.customer_trx_id <> NVL(gt.customer_trx_id, -99)
1459: invalid_value)
1460: SELECT trx_header_id,
1461: arp_standard.fnd_message('AR_INVALID_MANDATE_FLAG_VALUE'),
1462: trx_number
1463: FROM ar_trx_header_gt gt
1464: WHERE nvl(upper(mandate_last_trx_flag),'N') not in ('Y','N');
1465:
1466:
1467: IF pg_debug = 'Y'
1497: invalid_value)
1498: SELECT trx_header_id,
1499: arp_standard.fnd_message('AR_INAPI_INVALID_BATCH_SOURCE'),
1500: batch_source_id
1501: FROM ar_trx_header_gt gt
1502: WHERE NOT EXISTS (
1503: SELECT 'X'
1504: FROM ra_batch_sources bs
1505: where nvl(gt.trx_date, trunc(sysdate)) between
1543: invalid_value)
1544: SELECT trx_header_id,
1545: arp_standard.fnd_message('AR_INVALID_CURRENCY'),
1546: trx_currency
1547: FROM ar_trx_header_gt gt
1548: WHERE gt.trx_currency IS NOT NULL
1549: AND NOT EXISTS (
1550: SELECT 'X'
1551: FROM fnd_currencies c
1581: invalid_value)
1582: SELECT trx_header_id,
1583: arp_standard.fnd_message('AR_BR_TRX_TYPE_NULL'),
1584: cust_trx_type_id
1585: FROM ar_trx_header_gt gt
1586: WHERE gt.cust_trx_type_id IS NULL;
1587:
1588: INSERT INTO ar_trx_errors_gt (
1589: trx_header_id,
1591: invalid_value)
1592: SELECT trx_header_id,
1593: arp_standard.fnd_message('AR_INVALID_TRX_TYPE'),
1594: cust_trx_type_id
1595: FROM ar_trx_header_gt gt
1596: WHERE gt.cust_trx_type_id IS NOT NULL
1597: AND NOT EXISTS (
1598: SELECT 'X'
1599: FROM ra_cust_trx_types ctt
1632: invalid_value)
1633: SELECT trx_header_id,
1634: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_ID'),
1635: bill_to_customer_id
1636: FROM ar_trx_header_gt gt
1637: WHERE gt.bill_to_customer_id IS NOT NULL
1638: AND NOT EXISTS (
1639: SELECT 'X'
1640: FROM hz_cust_accounts ct
1671: invalid_value)
1672: SELECT trx_header_id,
1673: arp_standard.fnd_message('AR_INAPI_BILL_TO_SITE_ID_REQ'),
1674: bill_to_site_use_id
1675: FROM ar_trx_header_gt gt
1676: WHERE gt.bill_to_site_use_id IS NULL
1677: and gt.bill_to_customer_id is not null;
1678: end if;
1679:
1683: invalid_value)
1684: SELECT trx_header_id,
1685: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_SITE_USE'),
1686: bill_to_site_use_id
1687: FROM ar_trx_header_gt gt
1688: WHERE gt.bill_to_site_use_id IS NOT NULL
1689: AND NOT EXISTS (
1690: SELECT 'X'
1691: FROM hz_cust_site_uses ct
1729: invalid_value)
1730: SELECT trx_header_id,
1731: arp_standard.fnd_message('AR_INAPI_INVALID_BILL_ADDR_ID'),
1732: bill_to_address_id
1733: FROM ar_trx_header_gt gt
1734: WHERE gt.bill_to_address_id IS NOT NULL
1735: AND NOT EXISTS (
1736: SELECT 'X'
1737: FROM HZ_CUST_ACCT_SITES ACCT_SITE,
1784: invalid_value)
1785: SELECT trx_header_id,
1786: arp_standard.fnd_message('AR_INAPI_INV_SHIP_TO_CUST_ID'),
1787: ship_to_customer_id
1788: FROM ar_trx_header_gt gt
1789: WHERE gt.ship_to_customer_id IS NOT NULL
1790: AND NOT EXISTS (
1791: SELECT 'X'
1792: FROM hz_cust_accounts ct
1829: invalid_value)
1830: SELECT trx_header_id,
1831: arp_standard.fnd_message('AR_INAPI_SHIP_TO_SITE_ID_REQ'),
1832: ship_to_site_use_id
1833: FROM ar_trx_header_gt gt
1834: WHERE gt.ship_to_site_use_id IS NULL
1835: AND gt.ship_to_customer_id IS NOT NULL;
1836: end if;
1837:
1841: invalid_value)
1842: SELECT trx_header_id,
1843: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
1844: ship_to_site_use_id
1845: FROM ar_trx_header_gt gt
1846: WHERE gt.ship_to_site_use_id IS NOT NULL
1847: AND gt.ship_to_customer_id IS NOT NULL
1848: AND NOT EXISTS (
1849: SELECT 'X'
1861: invalid_value)
1862: SELECT trx_header_id,
1863: arp_standard.fnd_message('AR_INAPI_NULL_SHIP_TO_SITE'),
1864: ship_to_site_use_id
1865: FROM ar_trx_header_gt gt
1866: WHERE gt.ship_to_site_use_id IS NULL
1867: AND gt.ship_to_customer_id IS NOT NULL;
1868:
1869: end if;
1896: invalid_value)
1897: SELECT trx_header_id,
1898: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
1899: ship_to_site_use_id
1900: FROM ar_trx_header_gt gt
1901: WHERE gt.ship_to_site_use_id IS NOT NULL
1902: AND NOT EXISTS (
1903: select 'X'
1904: from
1947: -- do it for rows where it is not already populated. Moreover, first we
1948: -- should see if ship_to_address_id is populated then we should derive the
1949: -- ship_to_site_use_id from that.
1950:
1951: UPDATE ar_trx_header_gt gt
1952: SET ship_to_site_use_id = (
1953: SELECT site_use_id
1954: FROM hz_cust_site_uses
1955: WHERE site_use_code = 'SHIP_TO'
1961: AND gt.ship_to_address_id IS NOT NULL;
1962:
1963: /* 10041924 for those addresses that did not have active
1964: sites, use the max inactive one */
1965: UPDATE ar_trx_header_gt gt
1966: SET ship_to_site_use_id = (
1967: SELECT MAX(site_use_id)
1968: FROM hz_cust_site_uses
1969: WHERE site_use_code = 'SHIP_TO'
1977:
1978: -- Now we will worry about cases where only customer id
1979: -- is populated and we must derive the primary ship to site id.
1980:
1981: UPDATE ar_trx_header_gt gt
1982: SET ship_to_site_use_id = (
1983: SELECT site_use_id
1984: FROM hz_cust_site_uses
1985: WHERE primary_flag = 'Y'
2029: invalid_value)
2030: SELECT trx_header_id,
2031: arp_standard.fnd_message('AR_INAPI_INVALID_SHIP_SITE_USE'),
2032: gt.ship_to_site_use_id
2033: FROM ar_trx_header_gt gt
2034: WHERE gt.ship_to_customer_id IS NOT NULL
2035: AND gt.ship_to_site_use_id IS NOT NULL
2036: AND NOT EXISTS (
2037: select 'X'
2084: invalid_value)
2085: SELECT gt.trx_header_id,
2086: arp_standard.fnd_message('AR_INVALID_TERM'),
2087: gt.term_id
2088: FROM ar_trx_header_gt gt,
2089: ra_cust_trx_types ctt
2090: WHERE gt.term_id IS NOT NULL
2091: AND gt.trx_class <> 'CM' -- added for ER 5869149
2092: AND ctt.cust_trx_type_id = gt.cust_trx_type_id -- ER 5869149
2107: invalid_value)
2108: SELECT gt.trx_header_id,
2109: arp_standard.fnd_message('AR_INAPI_TERM_NOT_ALLOWED'),
2110: gt.term_id
2111: FROM ar_trx_header_gt gt,
2112: ra_cust_trx_types ctt
2113: WHERE gt.term_id IS NOT NULL
2114: AND gt.cust_trx_type_id = ctt.cust_trx_type_id
2115: AND ctt.type = 'CM';
2149: invalid_value)
2150: SELECT trx_header_id,
2151: arp_standard.fnd_message('AR_INVALID_PRIMARY_SALESREP'),
2152: primary_salesrep_id
2153: FROM ar_trx_header_gt gt
2154: WHERE gt.primary_salesrep_id IS NOT NULL
2155: AND NOT EXISTS (
2156: SELECT 'X'
2157: FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2189: invalid_value)
2190: SELECT trx_header_id,
2191: arp_standard.fnd_message(l_message_name),
2192: primary_salesrep_id
2193: FROM ar_trx_header_gt gt
2194: WHERE gt.primary_salesrep_id IS NOT NULL
2195: AND NOT EXISTS (
2196: (SELECT 'X'
2197: FROM ra_salesreps rs
2210: invalid_value)
2211: SELECT trx_header_id,
2212: arp_standard.fnd_message(l_message_name),
2213: primary_salesrep_id
2214: FROM ar_trx_header_gt gt
2215: WHERE gt.primary_salesrep_id IS NULL
2216: AND p_trx_system_param_rec.salesrep_required_flag = 'Y';
2217:
2218: IF pg_debug = 'Y'
2248: invalid_value)
2249: SELECT trx_header_id,
2250: arp_standard.fnd_message('AR_INAPI_INV_RULE_NOT_REQUIRED'),
2251: invoicing_rule_id
2252: FROM ar_trx_header_gt gt
2253: WHERE gt.invoicing_rule_id IS NOT NULL
2254: AND gt.cust_trx_type_id NOT IN (
2255: SELECT tt.cust_trx_type_id
2256: FROM ra_cust_trx_types tt
2263: invalid_value)
2264: SELECT trx_header_id,
2265: arp_standard.fnd_message('AR_INAPI_INVALID_INV_RULE_ID'),
2266: invoicing_rule_id
2267: FROM ar_trx_header_gt gt
2268: WHERE gt.invoicing_rule_id IS NOT NULL
2269: AND gt.invoicing_rule_id not in ( -2, -3);
2270:
2271: IF pg_debug = 'Y'
2301: invalid_value)
2302: SELECT trx_header_id,
2303: arp_standard.fnd_message('AR_INAPI_INVALID_PRINT_OPTION'),
2304: printing_option
2305: FROM ar_trx_header_gt gt
2306: WHERE gt.printing_option IS NOT NULL
2307: AND gt.printing_option NOT IN ( 'PRI', 'NOT');
2308: IF pg_debug = 'Y'
2309: THEN
2333: END IF;
2334:
2335: x_return_status := FND_API.G_RET_STS_SUCCESS;
2336:
2337: update ar_trx_header_gt gt
2338: set gt.printing_pending=decode(gt.PRINTING_OPTION,'PRI','Y','N');
2339:
2340: IF pg_debug = 'Y'
2341: THEN
2386: invalid_value)
2387: SELECT trx_header_id,
2388: arp_standard.fnd_message('AR_INAPI_INV_TAX_EXEMPT_FLAG'),
2389: default_tax_exempt_flag
2390: FROM ar_trx_header_gt gt
2391: WHERE default_tax_exempt_flag IS NOT NULL
2392: AND NOT EXISTS (
2393: select 'X'
2394: from ar_lookups AL1
2430: invalid_value)
2431: SELECT trx_header_id,
2432: arp_standard.fnd_message('AR_INAPI_INVALID_TRX_STATUS'),
2433: status_trx
2434: FROM ar_trx_header_gt
2435: WHERE status_trx IS NOT NULL
2436: AND status_trx not in ( 'OP','CL','PEN','VD');
2437:
2438: IF pg_debug = 'Y'
2467: invalid_value)
2468: SELECT trx_header_id,
2469: arp_standard.fnd_message('AR_INAPI_INVALID_FIN_CHARGE'),
2470: finance_charges
2471: FROM ar_trx_header_gt
2472: WHERE finance_charges IS NOT NULL
2473: AND finance_charges not in ( 'Y','N');
2474:
2475: IF pg_debug = 'Y'
2506: invalid_value)
2507: SELECT trx_header_id,
2508: arp_standard.fnd_message('AR_INAPI_INAVLID_CROSS_REF'),
2509: related_customer_trx_id
2510: FROM ar_trx_header_gt gt
2511: WHERE gt.related_customer_trx_id IS NOT NULL
2512: AND NOT EXISTS (
2513: SELECT 'X'
2514: FROM ra_customer_trx trx, ra_batch_sources bs, ar_lookups look,
2560: /* 5921925 - added post_to_gl logic */
2561: CURSOR cglDate IS
2562: select hdr.trx_header_id, hdr.gl_date, hdr.invoicing_rule_id,
2563: NVL(tt.post_to_gl, 'N') post_to_gl
2564: from ar_trx_header_gt hdr,
2565: ra_cust_trx_types tt
2566: where tt.cust_trx_type_id = hdr.cust_trx_type_id;
2567:
2568: l_period_status gl_period_statuses.closing_status%type DEFAULT 'U';
2695: invalid_value)
2696: SELECT trx_header_id,
2697: arp_standard.fnd_message('AR_INAPI_INVALID_AGREEMENT_ID'),
2698: agreement_id
2699: FROM ar_trx_header_gt gt
2700: WHERE gt.agreement_id IS NOT NULL
2701: AND NOT EXISTS (
2702: SELECT 'X'
2703: from hz_cust_accounts cust_acct,
2766: l_cust_trx_type_id,
2767: l_copy_doc_number_flag
2768: FROM RA_BATCH_SOURCES bs
2769: WHERE batch_source_id = (SELECT gt.batch_source_id
2770: FROM ar_trx_header_gt gt
2771: WHERE rownum =1 );
2772:
2773: IF pg_debug = 'Y'
2774: THEN
2781: -- ORASHID
2782: -- 21-AUG-2003 (END)
2783:
2784:
2785: UPDATE ar_trx_header_gt
2786: SET auto_batch_numbering_flag = l_bs_batch_auto_num_flag,
2787: auto_trx_numbering_flag = l_bs_auto_trx_num_flag,
2788: copy_doc_number_flag = l_copy_doc_number_flag,
2789: cust_trx_type_id = NVL(cust_trx_type_id, l_cust_trx_type_id),
2818: invalid_value)
2819: SELECT gt.trx_header_id,
2820: arp_standard.fnd_message('AR_INAPI_INVALID_BATCH_SOURCE'),
2821: gt.batch_source_id
2822: FROM ar_trx_header_gt gt;
2823: WHEN OTHERS THEN
2824: x_errmsg := 'Error in AR_INVOICE_UTILS.Get_batch_source_details '||sqlerrm;
2825: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2826: RETURN;
2844: l_trx_type ra_cust_trx_types.type%type;
2845:
2846: CURSOR cust_trx_type_c IS
2847: SELECT distinct cust_trx_type_id
2848: FROM ar_trx_header_gt;
2849: BEGIN
2850:
2851: IF pg_debug = 'Y'
2852: THEN
2895:
2896: -- Bug # 3099975
2897: -- ORASHID
2898: -- 21-AUG-2003 (END)
2899: UPDATE ar_trx_header_gt
2900: SET printing_option = nvl(printing_option,l_default_printing_option),
2901: status_trx = NVL(status_trx,l_default_status),
2902: allow_freight_flag = l_allow_freight_flag,
2903: tax_calculation_flag = l_tax_calculation_flag,
2917: invalid_value)
2918: SELECT gt.trx_header_id,
2919: arp_standard.fnd_message('ARTA_INVALID_TRX_TYPE'),
2920: gt.cust_trx_type_id
2921: FROM ar_trx_header_gt gt
2922: WHERE gt.cust_trx_type_id = cust_trx_type_rec.cust_trx_type_id;
2923:
2924: END;
2925: END LOOP;
2950: END IF;
2951:
2952: x_return_status := FND_API.G_RET_STS_SUCCESS;
2953: /*Bug8266696*/
2954: UPDATE ar_trx_header_gt gt
2955: SET gt.primary_salesrep_id =
2956: ( SELECT SU.PRIMARY_SALESREP_ID
2957: FROM HZ_CUST_ACCT_SITES ACCT_SITE,
2958: HZ_PARTY_SITES PARTY_SITE,
3001:
3002: -- if bill to customer number is passed then that should be used
3003: -- derive the bill to customer id.
3004:
3005: UPDATE ar_trx_header_gt gt
3006: SET gt.bill_to_customer_id =
3007: (SELECT cust_acct.cust_account_id
3008: FROM hz_cust_accounts cust_acct
3009: WHERE cust_acct.account_number = gt.bill_to_account_number
3016:
3017: -- if ship to customer number is passed then that should be used
3018: -- derive the bill to customer id.
3019:
3020: UPDATE ar_trx_header_gt gt
3021: SET gt.bill_to_customer_id =
3022: (SELECT cust_acct.cust_account_id
3023: FROM hz_cust_accounts cust_acct
3024: WHERE cust_acct.account_number = gt.ship_to_account_number
3031:
3032: -- if ship to customer id is passed then that should be used
3033: -- derive the bill to customer id.
3034:
3035: UPDATE ar_trx_header_gt gt
3036: SET gt.bill_to_customer_id = gt.ship_to_customer_id
3037: WHERE gt.bill_to_customer_id IS NULL
3038: AND gt.ship_to_customer_id IS NOT NULL;
3039:
3040:
3041: -- for the remaining rows bill to customer name should be used
3042: -- derive the bill to customer id.
3043:
3044: UPDATE ar_trx_header_gt gt
3045: SET gt.bill_to_customer_id =
3046: (SELECT cust_acct.cust_account_id
3047: FROM hz_cust_accounts cust_acct,
3048: hz_parties party
3085: -- do it for rows where it is not already populated. Moreover, first we
3086: -- should see if bill_to_address_id is populated then we should derive the
3087: -- bill_to_site_use_id from that.
3088:
3089: UPDATE ar_trx_header_gt gt
3090: SET bill_to_site_use_id = (
3091: SELECT site_use_id
3092: FROM hz_cust_site_uses
3093: WHERE site_use_code = 'BILL_TO'
3099: AND gt.bill_to_address_id IS NOT NULL;
3100:
3101: /* 10041924 for those addresses that did not have active
3102: sites, use the max inactive one */
3103: UPDATE ar_trx_header_gt gt
3104: SET bill_to_site_use_id = (
3105: SELECT MAX(site_use_id)
3106: FROM hz_cust_site_uses
3107: WHERE site_use_code = 'BILL_TO'
3115:
3116: -- Now we will worry about cases where only customer id
3117: -- is populated and we must derive the primary ship to site id.
3118:
3119: UPDATE ar_trx_header_gt gt
3120: SET bill_to_site_use_id = (
3121: SELECT site_use_id
3122: FROM hz_cust_site_uses
3123: WHERE primary_flag = 'Y'
3147:
3148: CURSOR cSiteUSe IS
3149: select sgt.bill_to_site_use_id, sgt.bill_to_customer_id,
3150: sgt.paying_customer_id
3151: from ar_trx_header_gt sgt
3152: WHERE sgt.bill_to_customer_id = sgt.paying_customer_id;
3153:
3154: BEGIN
3155: IF pg_debug = 'Y' THEN
3156: debug ('AR_INVOICE_UTILS.populate_paying_site_use_id(+)' );
3157: END IF;
3158:
3159: -- first populate paying_customer_id if it is null
3160: UPDATE ar_trx_header_gt
3161: set paying_customer_id = bill_to_customer_id
3162: WHERE paying_customer_id IS NULL;
3163:
3164: FOR cSiteUSeRec IN cSiteUSe
3162: WHERE paying_customer_id IS NULL;
3163:
3164: FOR cSiteUSeRec IN cSiteUSe
3165: LOOP
3166: UPDATE ar_trx_header_gt ugt
3167: set ugt.paying_site_use_id = cSiteUSeRec.bill_to_site_use_id
3168: WHERE ugt.paying_site_use_id IS NULL
3169: AND ugt.paying_customer_id = cSiteUSeRec.paying_customer_id;
3170:
3173:
3174: -- incase paying_customer_id and bill_to_customer_id is
3175: -- different
3176:
3177: UPDATE ar_trx_header_gt gt
3178: SET paying_site_use_id = (
3179: SELECT site_use_id
3180: FROM hz_cust_site_uses
3181: WHERE primary_flag = 'Y'
3207: -- do it for rows where it is not already populated. Moreover, first we
3208: -- should see if bill_to_site_use_id is populated then we should derive the
3209: -- bill_to_address_id from that.
3210:
3211: UPDATE ar_trx_header_gt gt
3212: SET bill_to_address_id = (
3213: SELECT site_use_id
3214: FROM hz_cust_site_uses
3215: WHERE site_use_code = 'BILL_TO'
3221:
3222: -- Now if it is still not populated then you default it from
3223: -- primary bill to.
3224:
3225: UPDATE ar_trx_header_gt gt
3226: SET gt.bill_to_address_id = (
3227: SELECT su.cust_acct_site_id
3228: FROM hz_cust_acct_sites acct_site,
3229: hz_party_sites party_site,
3264: l_site_use_id NUMBER;
3265:
3266: CURSOR HdrGtc IS
3267: SELECT distinct bill_to_site_use_id
3268: FROM ar_trx_header_gt
3269: WHERE bill_to_site_use_id IS NOT NULL
3270: AND remit_to_address_id IS NULL;
3271:
3272: -- Get country, state, and zip code info. for each bill to site
3302: debug ('Country ' || bill_to_site_rec.country);
3303: debug ('Postal code ' || bill_to_site_rec.postal_code);
3304: END IF;
3305:
3306: UPDATE ar_trx_header_gt gt
3307: SET remit_to_address_id = (
3308: SELECT acct_site.cust_acct_site_id
3309: FROM hz_cust_acct_sites acct_site,
3310: hz_party_sites party_site,
3374:
3375: CURSOR bill_to_customer_id_c IS
3376: SELECT distinct bill_to_customer_id,
3377: bill_to_address_id
3378: FROM ar_trx_header_gt
3379: WHERE bill_to_contact_id IS NULL;
3380: l_contact_id HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ROLE_ID%type;
3381: BEGIN
3382: IF pg_debug = 'Y'
3411: debug ('Bill to contact Id '|| l_contact_id);
3412: debug ('Bill to customer Id ' || bill_to_customer_id_rec.bill_to_customer_id);
3413: debug ('Bill to Address Id ' || bill_to_customer_id_rec.bill_to_address_id);
3414: END IF;
3415: UPDATE ar_trx_header_gt
3416: SET bill_to_contact_id = l_contact_id
3417: WHERE bill_to_customer_id = bill_to_customer_id_rec.bill_to_customer_id
3418: AND bill_to_address_id = bill_to_customer_id_rec.bill_to_address_id
3419: AND bill_to_contact_id IS NOT NULL;
3453:
3454: -- if bill to customer number is passed then that should be used
3455: -- derive the bill to customer id.
3456:
3457: UPDATE ar_trx_header_gt gt
3458: SET gt.ship_to_customer_id =
3459: (SELECT cust_acct.cust_account_id
3460: FROM hz_cust_accounts cust_acct
3461: WHERE cust_acct.account_number = gt.ship_to_account_number
3468:
3469: -- for the remaining rows bill to customer name should be used
3470: -- derive the bill to customer id.
3471:
3472: UPDATE ar_trx_header_gt gt
3473: SET gt.ship_to_customer_id =
3474: (SELECT cust_acct.cust_account_id
3475: FROM hz_cust_accounts cust_acct,
3476: hz_parties party
3514: -- do it for rows where it is not already populated. Moreover, first we
3515: -- should see if ship_to_site_use_id is populated then we should derive the
3516: -- ship_to_address_id from that.
3517:
3518: UPDATE ar_trx_header_gt gt
3519: SET ship_to_address_id = (
3520: SELECT site_use_id
3521: FROM hz_cust_site_uses
3522: WHERE site_use_code = 'SHIP_TO'
3529: -- Now we will worry about cases where only customer id
3530: -- is populated and we must derive the primary ship to site id.
3531:
3532:
3533: UPDATE ar_trx_header_gt gt
3534: SET gt.ship_to_address_id = (
3535: SELECT su.cust_acct_site_id
3536: FROM hz_cust_acct_sites acct_site,
3537: hz_party_sites party_site,
3571:
3572: CURSOR ship_to_customer_id_c IS
3573: SELECT distinct ship_to_customer_id,
3574: ship_to_address_id
3575: FROM ar_trx_header_gt
3576: WHERE ship_to_contact_id IS NULL;
3577: l_contact_id HZ_CUST_ACCOUNT_ROLES.CUST_ACCOUNT_ROLE_ID%type;
3578: BEGIN
3579: IF pg_debug = 'Y'
3610: debug ('Ship to Contact Id ' || l_contact_id);
3611: debug ('Ship to Customer Id ' || ship_to_customer_id_rec.ship_to_customer_id);
3612: debug ('Ship to Address Id ' || ship_to_customer_id_rec.ship_to_address_id);
3613: END IF;
3614: UPDATE ar_trx_header_gt
3615: SET ship_to_contact_id = l_contact_id
3616: WHERE ship_to_customer_id = ship_to_customer_id_rec.ship_to_customer_id
3617: AND ship_to_address_id = ship_to_customer_id_rec.ship_to_address_id;
3618:
3644: l_trx_date DATE;
3645: l_salesrep_id NUMBER;
3646: CURSOR cSalesTer IS
3647: SELECT /*+ LEADING(gt) */ st.territory_id territory_id, gt.primary_salesrep_id
3648: FROM ra_salesrep_territories st, ar_trx_header_gt gt
3649: WHERE st.salesrep_id = gt.primary_salesrep_id
3650: AND 'A' = NVL(st.status(+), 'A')
3651: AND gt.trx_date BETWEEN NVL(st.start_date_active(+), gt.trx_date )
3652: AND NVL(st.end_date_active(+), gt.trx_date );
3652: AND NVL(st.end_date_active(+), gt.trx_date );
3653:
3654: CURSOR cBillTo IS
3655: SELECT /*+ LEADING(gt) */ hz.territory_id, gt.bill_to_site_use_id
3656: FROM HZ_CUST_SITE_USES hz, ar_trx_header_gt gt
3657: WHERE hz.site_use_id = gt.bill_to_site_use_id;
3658:
3659: CURSOR cShipTo IS
3660: SELECT /*+ LEADING(gt) */ site_uses.territory_id, gt.ship_to_site_use_id,
3659: CURSOR cShipTo IS
3660: SELECT /*+ LEADING(gt) */ site_uses.territory_id, gt.ship_to_site_use_id,
3661: gt.ship_to_customer_id
3662: FROM HZ_CUST_SITE_USES site_uses,
3663: ar_trx_header_gt gt
3664: WHERE site_uses.SITE_USE_CODE = 'SHIP_TO'
3665: AND site_uses.site_use_id = gt.ship_to_site_use_id
3666: AND site_uses.primary_flag = 'Y';
3667:
3682: IF p_trx_system_param_rec.default_territory = 'BILL'
3683: THEN
3684: FOR cBillToRec IN cBillTo
3685: LOOP
3686: UPDATE ar_trx_header_gt
3687: SET territory_id = cBillToRec.territory_id
3688: WHERE bill_to_site_use_id = cBillToRec.bill_to_site_use_id;
3689: END LOOP;
3690: ELSIF p_trx_system_param_rec.default_territory = 'SHIP'
3690: ELSIF p_trx_system_param_rec.default_territory = 'SHIP'
3691: THEN
3692: FOR cShipToRec IN cShipTo
3693: LOOP
3694: UPDATE ar_trx_header_gt
3695: SET territory_id = cShipToRec.territory_id
3696: WHERE ship_to_site_use_id = cShipToRec.ship_to_site_use_id
3697: AND ship_to_customer_id = cShipToRec.ship_to_customer_id;
3698: END LOOP;
3699: ELSIF p_trx_system_param_rec.default_territory = 'SALES'
3700: THEN
3701: FOR cSalesTerRec IN cSalesTer
3702: LOOP
3703: UPDATE ar_trx_header_gt
3704: SET territory_id = cSalesTerRec.territory_id
3705: WHERE primary_salesrep_id = cSalesTerRec.primary_salesrep_id;
3706: END LOOP;
3707: END IF;
3729: debug ('AR_INVOICE_UTILS.populate_legal_entity(+)' );
3730: END IF;
3731:
3732: /* single update to default value */
3733: UPDATE ar_trx_header_gt
3734: SET legal_entity_id = arp_legal_entity_util.get_default_le(
3735: sold_to_customer_id,
3736: bill_to_customer_id,
3737: cust_trx_type_id,
3784: debug ('Getting term_id(+)' );
3785: END IF;
3786:
3787: -- Get term_id for NON-BFB enabled customers
3788: UPDATE ar_trx_header_gt gt
3789: SET term_id = (SELECT tl.term_id
3790: FROM ra_terms t_su,
3791: ra_terms t_cp1,
3792: ra_terms t_cp2,
3822: AND gt.trx_class in ('INV','DM') -- added for ER 5869149
3823: AND ar_bfb_utils_pvt.get_bill_level(gt.bill_to_customer_id) = 'N';
3824:
3825: -- R12:BFB : get term_id for BFB-enabled customers
3826: UPDATE ar_trx_header_gt gt
3827: SET term_id = ar_bfb_utils_pvt.get_default_term(
3828: gt.cust_trx_type_id,
3829: gt.trx_date,
3830: gt.org_id,
3839: debug ('Getting term_id(-)' );
3840: debug ('Getting billing_date(+)');
3841: END IF;
3842:
3843: UPDATE ar_trx_header_gt gt
3844: SET billing_date = ar_bfb_utils_pvt.get_billing_date
3845: (ar_bfb_utils_pvt.get_billing_cycle(gt.term_id),
3846: nvl(gt.trx_date,sysdate))
3847: WHERE gt.term_id IS NOT NULL
3851: AND nvl(ar_bfb_utils_pvt.get_cycle_type
3852: (ar_bfb_utils_pvt.get_billing_cycle(term_id)),'XXX') = 'RECURRING';
3853:
3854: -- override incorrect billing dates
3855: UPDATE ar_trx_header_gt gt
3856: SET billing_date = ar_bfb_utils_pvt.get_billing_date
3857: (ar_bfb_utils_pvt.get_billing_cycle(gt.term_id),
3858: gt.billing_date)
3859: WHERE gt.term_id IS NOT NULL
3875: -- ORASHID
3876: -- 21-AUG-2003 (END)
3877:
3878: -- get due_date for NON-BFB enabled
3879: UPDATE ar_trx_header_gt gt
3880: SET term_due_date = trunc(arpt_sql_func_util.get_First_Due_Date(
3881: gt.term_id, NVL(gt.trx_date,sysdate)))
3882: WHERE gt.term_id IS NOT NULL
3883: AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'N'
3886: FROM ar_trx_errors_gt errgt
3887: WHERE errgt.trx_header_id = gt.trx_header_id);
3888:
3889: -- get due_date for BFB enabled
3890: UPDATE ar_trx_header_gt gt
3891: SET term_due_date = ar_bfb_utils_pvt.get_due_date(gt.billing_date, gt.term_id)
3892: WHERE gt.term_id IS NOT NULL
3893: AND ar_bfb_utils_pvt.is_payment_term_bfb(gt.term_id) = 'Y'
3894: AND gt.billing_date IS NOT NULL
3932: invalid_value)
3933: SELECT trx_header_id,
3934: arp_standard.fnd_message('AR_INAPI_INV_BILL_TO_CUST_ID'),
3935: bill_to_address_id
3936: FROM ar_trx_header_gt gt
3937: WHERE gt.bill_to_customer_id IS NULL;
3938: END IF;
3939: populate_ship_to_site_use_id(
3940: x_errmsg => x_errmsg,
3963: invalid_value)
3964: SELECT trx_header_id,
3965: arp_standard.fnd_message('AR_INAPI_INVALID_BILL_ADDR_ID'),
3966: bill_to_address_id
3967: FROM ar_trx_header_gt gt
3968: WHERE gt.bill_to_address_id IS NULL;
3969: END IF;
3970: IF pg_debug = 'Y'
3971: THEN
4152: SELECT gt.trx_header_id,
4153: gt.trx_line_id,
4154: arp_standard.fnd_message('AR_INAPI_CM_UOM_NOT_ALLOWED'),
4155: gt.uom_code
4156: FROM ar_trx_lines_gt gt, ar_trx_header_gt gt2
4157: WHERE gt.uom_code IS NOT NULL
4158: AND gt.line_type = 'LINE'
4159: AND gt2.trx_header_id = gt.trx_header_id
4160: AND gt2.trx_class = 'CM'; -- added for ER 5869149
4167: SELECT gt.trx_header_id,
4168: gt.trx_line_id,
4169: arp_standard.fnd_message('AR_INAPI_INVALID_UOM'),
4170: gt.uom_code
4171: FROM ar_trx_lines_gt gt , ar_trx_header_gt gt2
4172: WHERE gt.uom_code IS NOT NULL
4173: AND gt.line_type = 'LINE'
4174: AND gt2.trx_header_id = gt.trx_header_id
4175: AND gt2.trx_class <> 'CM' -- added for ER 5869149
4570: error_message)
4571: SELECT gt.trx_header_id,
4572: arp_standard.fnd_message('AR_INAPI_QTY_NOT_NULL')
4573: FROM ar_trx_lines_gt gt ,
4574: ar_trx_header_gt gt2
4575: WHERE gt.quantity_invoiced IS NULL
4576: AND gt.trx_header_id = gt2.trx_header_id
4577: AND gt2.trx_class <> 'CM' -- added for ER 5869149
4578: AND gt.line_type = 'LINE';
4605: error_message)
4606: SELECT gt.trx_header_id,
4607: arp_standard.fnd_message('AR_INAPI_UNIT_PRICE_NOT_NULL')
4608: FROM ar_trx_lines_gt gt ,
4609: ar_trx_header_gt gt2
4610: WHERE gt.unit_selling_price IS NULL
4611: AND gt.trx_header_id = gt2.trx_header_id
4612: AND gt2.trx_class <> 'CM' -- Added for ER 5869149
4613: AND gt.line_type = 'LINE';
4757:
4758: BEGIN
4759: SELECT nvl(gt.allow_freight_flag, 'N'), gt.cust_trx_type_id
4760: INTO l_allow_freight_flag, l_cust_trx_type_id
4761: FROM ar_trx_header_gt gt
4762: WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
4763:
4764: IF l_allow_freight_flag = 'N'
4765: THEN
4802: ( trx_header_id,
4803: error_message)
4804: SELECT trx_header_id,
4805: arp_standard.fnd_message('AR_TAPI_TOO_MANY_FREIGHT_LINE')
4806: FROM ar_trx_header_gt gt
4807: WHERE gt.trx_header_id = c_freight_rec.trx_header_id;
4808: END IF;
4809: END IF;
4810: END LOOP;
4901: gt.revenue_amount, gt.quantity_invoiced,
4902: gt.unit_selling_price,
4903: gt.currency_code -- Bug 13635700, FP bug of 9063570
4904: FROM ar_trx_lines_gt gt,
4905: ar_trx_header_gt gt2
4906: WHERE gt.line_type ='LINE'
4907: AND gt2.trx_header_id = gt.trx_header_id;
4908: --AND gt2.trx_class = 'CM'; --Commented Since the below Check has to be performed for both CM and INV
4909:
4911: SELECT gt.trx_header_id, gt2.creation_sign,
4912: sum(gt.extended_amount) extended_amount,
4913: sum(gt.revenue_amount) revenue_amount
4914: FROM ar_trx_lines_gt gt,
4915: ar_trx_header_gt gt2
4916: WHERE gt.line_type ='LINE'
4917: AND gt2.trx_header_id = gt.trx_header_id
4918: group by gt.trx_header_id, gt2.creation_sign ;
4919: --commented for bug 8731646
5044: ( trx_header_id,
5045: error_message)
5046: SELECT trx_header_id,
5047: arp_standard.fnd_message('AR_INAPI_DUP_DOC_SEQUENCE')
5048: FROM ar_trx_header_gt gt
5049: WHERE gt.cust_trx_type_id IS NOT NULL
5050: AND gt.doc_sequence_value IS NOT NULL
5051: AND EXISTS (
5052: SELECT 'Y' --already exists
5088: x_return_status OUT NOCOPY VARCHAR2) IS
5089:
5090: CURSOR ctrxHeader IS
5091: SELECT * FROM
5092: ar_trx_header_gt
5093: WHERE trx_header_id NOT IN (
5094: SELECT trx_header_id FROM
5095: ar_trx_errors_gt);
5096:
5233:
5234: IF l_update_trx
5235: THEN
5236:
5237: UPDATE ar_trx_header_gt
5238: SET doc_sequence_value = l_doc_sequence_value,
5239: doc_sequence_id = l_doc_sequence_id,
5240: trx_number = DECODE(ctrxHeaderRec.copy_doc_number_flag,
5241: 'Y',NVL(to_char(l_doc_sequence_value),
5274: x_return_status OUT NOCOPY VARCHAR2) IS
5275:
5276: CURSOR ctrxHeader IS
5277: SELECT * FROM
5278: ar_trx_header_gt
5279: WHERE trx_header_id NOT IN (
5280: SELECT trx_header_id FROM
5281: ar_trx_errors_gt)
5282: AND payment_trxn_extension_id is not null;
5379: o_payment_trxn_extension_id := p_trxn_entity_id ;
5380:
5381: arp_standard.debug('the copied value of trx_entn is ' || o_payment_trxn_extension_id );
5382:
5383: UPDATE ar_trx_header_gt
5384: SET payment_trxn_extension_id = o_payment_trxn_extension_id
5385: WHERE trx_header_id = ctrxHeaderRec.trx_header_id;
5386: END IF;
5387:
5510: x_return_status OUT NOCOPY VARCHAR2) IS
5511:
5512: Cursor cTerritory IS
5513: SELECT territory_id, trx_header_id
5514: FROm ar_trx_header_gt
5515: WHERE territory_id IS NOT NULL;
5516:
5517: BEGIN
5518: IF pg_debug = 'Y'
5614: SELECT line.trx_header_id, line.trx_line_id, hdr.invoicing_rule_id,
5615: line.ACCOUNTING_RULE_ID, line.ACCOUNTING_RULE_DURATION,
5616: line.RULE_START_DATE,line.RULE_END_DATE,line.set_of_books_id,hdr.trx_date,
5617: rr.type, rr.frequency, rr.occurrences
5618: FROM ar_trx_lines_gt line, ar_trx_header_gt hdr,
5619: ra_rules rr
5620: WHERE hdr.invoicing_rule_id IS NOT NULL
5621: AND line.accounting_rule_id IS NOT NULL
5622: AND hdr.trx_header_id = line.trx_header_id
5640: error_message)
5641: SELECT gt.trx_header_id,
5642: gt.trx_line_id,
5643: arp_standard.fnd_message('AR_INAPI_INVALID_RULE_NAME')
5644: FROM ar_trx_lines_gt gt , ar_trx_header_gt gt2
5645: WHERE gt.accounting_rule_id IS NOT NULL
5646: AND gt2.trx_header_id = gt.trx_header_id
5647: AND gt2.trx_class <> 'CM' -- Added for ER 5869149
5648: AND NOT EXISTS (
5658: error_message)
5659: SELECT gt.trx_header_id,
5660: gt.trx_line_id,
5661: arp_standard.fnd_message('AR_INAPI_RULE_NAME_NOT_NULL')
5662: FROM ar_trx_lines_gt gt, ar_trx_header_gt gth
5663: WHERE gt.accounting_rule_id IS NULL
5664: AND gt.trx_header_id = gth.trx_header_id
5665: AND gth.invoicing_rule_id IS NOT NULL
5666: AND gth.trx_class <> 'CM' -- Added for ER 5869149
5674: error_message)
5675: SELECT gt.trx_header_id,
5676: gt.trx_line_id,
5677: arp_standard.fnd_message('AR_INAPI_RULE_NOT_ALLOWED')
5678: FROM ar_trx_lines_gt gt, ar_trx_header_gt gth
5679: WHERE (gt.accounting_rule_id IS NOT NULL
5680: OR gth.invoicing_rule_id IS NOT NULL)
5681: AND gt.trx_header_id = gth.trx_header_id
5682: AND gth.trx_class = 'CM' -- Added for ER 5257046
5999: SELECT trx_header_id, trx_currency,
6000: nvl(exchange_rate_type,
6001: p_trx_profile_rec.default_exchange_rate_type) exchange_rate_type,
6002: trunc(nvl(exchange_date,trx_date)) exchange_date, exchange_rate
6003: FROM ar_trx_header_gt gt
6004: WHERE p_trx_system_parameters_rec.base_currency_code <>
6005: trx_currency
6006: AND nvl(exchange_rate_type,
6007: p_trx_profile_rec.default_exchange_rate_type) <> 'User'
6062: THEN
6063: debug ( ' Exchange Rate ' || l_exchange_rate);
6064: END IF;
6065:
6066: UPDATE ar_trx_header_gt
6067: SET exchange_rate = l_exchange_rate,
6068: exchange_date = cExchangeRateRec.exchange_date,
6069: exchange_rate_type = cExchangeRateRec.exchange_rate_type
6070: WHERE trx_header_id = cExchangeRateRec.trx_header_id;
6085: END LOOP;
6086:
6087: -- Update the exchange_date in case exchange_rate_type is 'User'
6088: -- and no exchange date has been provided.
6089: UPDATE ar_trx_header_gt
6090: SET exchange_date = trunc(trx_date)
6091: WHERE exchange_rate_type = 'User'
6092: AND exchange_date IS NULL;
6093:
6099: invalid_value)
6100: SELECT trx_header_id,
6101: arp_standard.fnd_message('AR_EXCHANGE_RATE_NEEDED'),
6102: gt.trx_currency
6103: FROM ar_trx_header_gt gt
6104: WHERE gt.trx_currency IS NOT NULL
6105: AND ( exchange_rate IS NULL
6106: OR exchange_rate <= 0
6107: OR exchange_date IS NULL)
6129: SELECT trx_currency, paying_customer_id,
6130: paying_site_use_id, bill_to_customer_id,
6131: bill_to_site_use_id, trx_date, trx_header_id,
6132: payment_trxn_extension_id
6133: FROM ar_trx_header_gt
6134: WHERE receipt_method_id IS NULL;
6135: --AND payment_trxn_extension_id IS NOT NULL;
6136: l_receipt_method_name ar_receipt_methods.name%type;
6137: l_receipt_method_id ar_receipt_methods.receipt_method_id%type;
6166: IF l_creation_method_code = 'AUTOMATIC'
6167: AND l_receipt_method_id IS NOT NULL
6168: AND cCustDetailsRec.payment_trxn_extension_id IS NOT NULL
6169: THEN
6170: UPDATE ar_trx_header_gt
6171: set receipt_method_id = l_receipt_method_id
6172: WHERE trx_header_id = cCustDetailsRec.trx_header_id;
6173: END IF;
6174:
6174:
6175: IF l_creation_method_code = 'MANUAL'
6176: AND l_receipt_method_id IS NOT NULL
6177: THEN
6178: UPDATE ar_trx_header_gt
6179: set receipt_method_id = l_receipt_method_id
6180: WHERE trx_header_id = cCustDetailsRec.trx_header_id;
6181: END IF;
6182:
6195: SELECT trx_currency, paying_customer_id, paying_site_use_id,
6196: bill_to_customer_id, bill_to_site_use_id, trx_date,
6197: trx_header_id, org_id, trx_number, customer_trx_id,
6198: mandate_last_trx_flag
6199: FROM ar_trx_header_gt
6200: WHERE receipt_method_id IS NULL
6201: AND payment_trxn_extension_id IS NULL;
6202:
6203: l_receipt_method_name ar_receipt_methods.name%type;
6310: x_response => l_response);
6311:
6312: -- The values are based on FND_API. S, E, U (Success, Error, Unexpected
6313: IF (l_return_status = 'S') THEN
6314: UPDATE ar_trx_header_gt
6315: set receipt_method_id = l_receipt_method_id,
6316: payment_trxn_extension_id=l_entity_id
6317: WHERE trx_header_id = cCustDetailsRec.trx_header_id;
6318:
7267: C.MINIMUM_ACCOUNTABLE_UNIT
7268: )
7269: FROM FND_CURRENCIES C,
7270: ar_trx_LINES_GT L,
7271: ar_trx_header_gt h
7272: WHERE C.CURRENCY_CODE = p_trx_system_parameters_rec.base_currency_code
7273: AND L.trx_LINE_ID = D.trx_LINE_ID
7274: AND L.trx_header_id = h.trx_header_id
7275: )
7333: SELECT d.trx_header_id,
7334: d.trx_line_ID,
7335: arp_standard.fnd_message('AR_INAPI_100_PERCENT'),
7336: sum(d.percent)
7337: FROM ar_trx_dist_gt d, ar_trx_lines_gt L, ar_trx_header_gt h
7338: WHERE d.trx_line_id = l.trx_line_id
7339: AND l.trx_header_id = h.trx_header_id
7340: GROUP BY d.trx_header_id,d.trx_line_ID, ACCOUNT_CLASS
7341: HAVING SUM(d.PERCENT) <> 100;
7384: -- update accounting set flag
7385: UPDATE ar_trx_dist_gt dgt
7386: SET dgt.account_set_flag =
7387: (SELECT DECODE(hgt.invoicing_rule_id,null,'N','Y')
7388: FROM ar_trx_header_gt hgt, ar_trx_lines_gt lgt
7389: WHERE hgt.trx_header_id = lgt.trx_header_id
7390: AND lgt.trx_line_id = dgt.trx_line_id
7391: AND dgt.account_class <> 'REC'
7392: UNION
7390: AND lgt.trx_line_id = dgt.trx_line_id
7391: AND dgt.account_class <> 'REC'
7392: UNION
7393: SELECT DECODE(h.invoicing_rule_id,null,'N','Y')
7394: FROM ar_trx_header_gt h
7395: WHERE h.trx_header_id = dgt.trx_header_id
7396: AND dgt.account_class = 'REC');
7397:
7398: -- Now update the amount
7519: l_min_acc_unit)),
7520: D.ACCTD_AMOUNT),
7521: NULL))
7522: FROM ar_trx_DIST_gt D,
7523: ar_trx_header_gt h
7524: --RA_CUST_TRX_TYPES TYPE,
7525: FND_CURRENCIES C,
7526: GL_SETS_OF_BOOKS G,
7527: --RA_CUSTOMER_TRX PREV_TRX,
7566: /*4673387*/
7567: UPDATE ar_trx_dist_gt
7568: SET gl_date = NULL
7569: WHERE trx_header_id IN (SELECT trx_header_id
7570: FROM ar_trx_header_gt gt,
7571: ra_cust_trx_types ctt
7572: WHERE ctt.cust_trx_type_id = gt.cust_trx_type_id
7573: AND ctt.post_to_gl = 'N');
7574: