eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AR_AUTOREC_API dependencies on AR_RECEIPTS_GT

Line 128: from AR_RECEIPTS_GT gt

124: select payment_schedule_id,
125: receipt_number rec_num,
126: cash_receipt_id rec_id,
127: amount_due_remaining amt
128: from AR_RECEIPTS_GT gt
129: where payment_schedule_id <> -99999
130: order by amt;
131:
132: /* apply variables */

Line 336: where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);

332:
333:
334: update ar_payment_schedules
335: set selected_for_receipt_batch_id = o_batch_id
336: where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);
337:
338: arp_standard.debug ( 'NO of PS rows updated = '|| to_char(SQL%ROWCOUNT));
339:
340: IF PG_DEBUG in ('Y', 'C') THEN

Line 406: where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);

402:
403:
404: update ar_payment_schedules
405: set selected_for_receipt_batch_id = o_batch_id
406: where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);
407:
408: arp_standard.debug ( 'NO of PS rows updated = '|| to_char(SQL%ROWCOUNT));
409:
410: update ar_batches SET

Line 649: ar_receipts_gt rec

645: CURSOR c3 is
646: select distinct cr.cash_receipt_id cr_id,
647: cr.payment_trxn_extension_id pmt_trxn_id
648: from ar_cash_receipts cr,
649: ar_receipts_gt rec
650: where rec.cash_receipt_id = cr.cash_receipt_id;
651:
652:
653:

Line 729: FROM ar_receipts_gt r,

725: program_application_id= l_program_application_id,
726: program_id = l_program_id,
727: program_update_date = sysdate
728: WHERE customer_trx_id in (SELECT r.customer_trx_id
729: FROM ar_receipts_gt r,
730: ar_cash_receipts rec
731: WHERE r.receipt_number = rec.receipt_number
732: AND rec.cash_receipt_id = CREC.cr_id);
733:

Line 770: FROM ar_receipts_gt r,

766: program_application_id= l_program_application_id,
767: program_id = l_program_id,
768: program_update_date = sysdate
769: WHERE customer_trx_id in (SELECT r.customer_trx_id
770: FROM ar_receipts_gt r,
771: ar_cash_receipts rec
772: WHERE r.receipt_number = rec.receipt_number
773: AND rec.cash_receipt_id = CREC.cr_id);
774:

Line 832: AR_RECEIPTS_GT);

828: program_id = l_program_id,
829: program_update_date = sysdate
830: WHERE cash_receipt_id in (
831: select cash_receipt_id from
832: AR_RECEIPTS_GT);
833:
834: arp_standard.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
835:
836: /* CALL TO CONTROL_CHECK to detect bad receipts */

Line 863: AR_RECEIPTS_GT);

859: program_id = l_program_id,
860: program_update_date = sysdate
861: WHERE cash_receipt_id in (
862: select cash_receipt_id from
863: AR_RECEIPTS_GT);
864:
865: arp_standard.debug ( 'NO of Receipts updated CRH = '|| to_char(SQL%ROWCOUNT));
866:
867:

Line 879: AR_RECEIPTS_GT);

875: program_id = l_program_id,
876: program_update_date = sysdate
877: WHERE cash_receipt_id in (
878: select cash_receipt_id from
879: AR_RECEIPTS_GT);
880:
881: arp_standard.debug ( 'NO of Receipts updated PS = '|| to_char(SQL%ROWCOUNT));
882:
883:

Line 895: AR_RECEIPTS_GT);

891: program_id = l_program_id,
892: program_update_date = sysdate
893: WHERE cash_receipt_id in (
894: select cash_receipt_id from
895: AR_RECEIPTS_GT);
896:
897: /* UPDATING INVOICE PS 7271561*/
898: update ar_payment_schedules
899: set selected_for_receipt_batch_id = NULL

Line 900: where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);

896:
897: /* UPDATING INVOICE PS 7271561*/
898: update ar_payment_schedules
899: set selected_for_receipt_batch_id = NULL
900: where payment_schedule_id in (select PAYMENT_SCHEDULE_ID from AR_RECEIPTS_GT);
901:
902: IF l_rec_creation_rule_code = 'PER_INVOICE' AND
903: l_rec_inher_inv_num_flag = 'Y' THEN
904: update ar_cash_receipts cr SET

Line 916: select cash_receipt_id from ar_receipts_gt)

912: and ps.customer_trx_id = app.applied_customer_trx_id
913: and app.status = 'APP'
914: and rownum =1),receipt_number)
915: WHERE cash_receipt_id in (
916: select cash_receipt_id from ar_receipts_gt)
917: AND request_id = l_request_id;
918:
919: arp_standard.debug ( 'NO of Receipts updated = '|| to_char(SQL%ROWCOUNT));
920: END IF;

Line 1170: | into the GT table AR_RECEIPTS_GT |

1166: | |
1167: | DESCRIPTION |
1168: | |
1169: | This procedure is used to select the valied invoices and insert them |
1170: | into the GT table AR_RECEIPTS_GT |
1171: | PSEUDO CODE/LOGIC |
1172: | |
1173: | PARAMETERS |
1174: | |

Line 1553: -- Bulk Insert into AR_RECEIPTS_GT

1549: dbms_sql.close_cursor( trx_invoices);
1550:
1551: fnd_file.put_line(fnd_file.log,'Number of Rows Fetched :'||l_rows_fetched);
1552: IF l_rows_fetched > 0 THEN -- Changed as per Bug:5331158 for avoiding error ORA-06569
1553: -- Bulk Insert into AR_RECEIPTS_GT
1554:
1555:
1556: BEGIN
1557:

Line 1558: inst_stmt := 'insert into ar_receipts_gt values ( :psid_array,:trxid_array,:crid_array,

1554:
1555:
1556: BEGIN
1557:
1558: inst_stmt := 'insert into ar_receipts_gt values ( :psid_array,:trxid_array,:crid_array,
1559: :paycust_array,:paysite_array,:pmt_trxn_id_array,
1560: :duedate_array,:amtdue_array,:custbank_array,
1561: :cust_amt_array,null,:pmt_channel_array,:pmt_instr_array,null)';
1562: rec_t := dbms_sql.open_cursor;

Line 1836: FROM ar_receipts_gt

1832: DECLARE
1833:
1834: CURSOR C_REC1 IS
1835: SELECT DISTINCT payment_schedule_id
1836: FROM ar_receipts_gt
1837: WHERE PAYMENT_SCHEDULE_ID is not null;
1838:
1839: BEGIN
1840:

Line 1897: UPDATE AR_RECEIPTS_GT

1893: IF (l_doc_sequence_value is not NULL)
1894: THEN
1895: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
1896:
1897: UPDATE AR_RECEIPTS_GT
1898: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
1899: WHERE payment_schedule_id = cust1.payment_schedule_id;
1900:
1901: arp_standard.debug (to_char(SQL%ROWCOUNT) || ' row(s) updated.');

Line 1926: update ar_receipts_gt arg

1922: END LOOP;
1923: ELSE
1924: arp_standard.debug ('receipt_inherit_inv_num_flag : Y');
1925:
1926: update ar_receipts_gt arg
1927: set receipt_number = (select trx_number||decode(terms_sequence_number,1,'','-'||terms_sequence_number)
1928: from ar_payment_schedules ps
1929: where ps.payment_schedule_id = arg.payment_schedule_id
1930: and rownum = 1)

Line 1965: from AR_RECEIPTS_GT

1961: paying_customer_id pay_cust_id,
1962: payment_trxn_extension_id pmt_trxn_ext_id,
1963: paying_site_use_id pay_site_use_id,
1964: sum(amount_due_remaining) amt
1965: from AR_RECEIPTS_GT
1966: group by receipt_number,customer_trx_id,payment_schedule_id,paying_customer_id,payment_trxn_extension_id, paying_site_use_id;
1967:
1968: /* calling create cash */
1969:

Line 2009: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2005: p_exception_code => l_err_code ,
2006: p_additional_message => l_err_code
2007: );
2008: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2009: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2010: where receipt_number = R2.rec_num);
2011: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num;
2012: END IF;
2013:

Line 2011: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num;

2007: );
2008: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2009: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2010: where receipt_number = R2.rec_num);
2011: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num;
2012: END IF;
2013:
2014: EXCEPTION
2015: WHEN NO_DATA_FOUND THEN -- NO_DATA_FOUND Condition as per Bug:5346610

Line 2062: Update ar_receipts_gt set cash_receipt_id = l_cr_id

2058: );
2059: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
2060: arp_standard.debug('x_return_status: '||l_return_status);
2061: IF l_return_status = 'S' THEN
2062: Update ar_receipts_gt set cash_receipt_id = l_cr_id
2063: where receipt_number = R2.rec_num
2064: and customer_trx_id = R2.customer_trx_id
2065: and payment_schedule_id = R2.payment_schedule_id;
2066: END IF;

Line 2070: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2066: END IF;
2067: IF l_return_status <> 'S' THEN
2068: /*bug7117223 start*/
2069: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2070: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2071: where receipt_number = R2.rec_num
2072: and customer_trx_id = R2.customer_trx_id
2073: and payment_schedule_id = R2.payment_schedule_id);
2074: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num

Line 2074: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num

2070: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2071: where receipt_number = R2.rec_num
2072: and customer_trx_id = R2.customer_trx_id
2073: and payment_schedule_id = R2.payment_schedule_id);
2074: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R2.rec_num
2075: and customer_trx_id = R2.customer_trx_id
2076: and payment_schedule_id = R2.payment_schedule_id;
2077: /*bug7117223 end*/
2078: IF l_msg_count = 1 Then

Line 2141: FROM AR_RECEIPTS_GT

2137: DECLARE
2138:
2139: CURSOR C_REC2 IS
2140: SELECT paying_customer_id,payment_instrument
2141: FROM AR_RECEIPTS_GT
2142: WHERE PAYMENT_SCHEDULE_ID is not null
2143: Group by paying_customer_id,payment_instrument;
2144:
2145:

Line 2201: UPDATE AR_RECEIPTS_GT

2197: THEN
2198: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
2199:
2200:
2201: UPDATE AR_RECEIPTS_GT
2202: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
2203: WHERE paying_customer_id = cust2.paying_customer_id
2204: AND payment_instrument = cust2.payment_instrument;
2205:

Line 2254: from AR_RECEIPTS_GT

2250: CURSOR c1 is
2251: select receipt_number rec_num,
2252: paying_customer_id pay_cust_id,
2253: sum(amount_due_remaining) amt
2254: from AR_RECEIPTS_GT
2255: group by receipt_number,paying_customer_id;
2256:
2257: /* calling create cash */
2258:

Line 2292: from AR_RECEIPTS_GT

2288:
2289: DECLARE
2290: cursor c1_inv is
2291: select payment_schedule_id
2292: from AR_RECEIPTS_GT
2293: where receipt_number = R1.rec_num;
2294: BEGIN
2295: FOR R1_inv in c1_inv loop
2296: insert_exceptions(

Line 2307: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2303: );
2304: END LOOP;
2305: END;
2306: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2307: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2308: where receipt_number = R1.rec_num);
2309: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;
2310: END IF;
2311:

Line 2309: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;

2305: END;
2306: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2307: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2308: where receipt_number = R1.rec_num);
2309: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;
2310: END IF;
2311:
2312: EXCEPTION
2313: when NO_DATA_FOUND then -- NO_DATA_FOUND Condition added as per Bug:5346610

Line 2324: from ar_receipts_gt

2320:
2321: IF l_err_code <> 'ARZCAR_CUST_MIN_AMT' OR l_err_code IS NULL THEN -- Condition as per Bug:5346610
2322: select payment_trxn_extension_id
2323: into l_payment_trxn_extension_id
2324: from ar_receipts_gt
2325: where receipt_number = R1.rec_num
2326: and paying_customer_id = R1.pay_cust_id
2327: and rownum = 1;
2328:

Line 2362: Update ar_receipts_gt set cash_receipt_id = l_cr_id

2358: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
2359: arp_standard.debug('x_return_status: '||l_return_status);
2360:
2361: IF l_return_status = 'S' THEN
2362: Update ar_receipts_gt set cash_receipt_id = l_cr_id
2363: where receipt_number = R1.rec_num;
2364: END IF;
2365: IF l_return_status <> 'S' THEN
2366: /*bug7117223 start*/

Line 2368: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2364: END IF;
2365: IF l_return_status <> 'S' THEN
2366: /*bug7117223 start*/
2367: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2368: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2369: where receipt_number = R1.rec_num);
2370: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;
2371: /*bug7117223 end*/
2372: IF l_msg_count = 1 Then

Line 2370: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;

2366: /*bug7117223 start*/
2367: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2368: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2369: where receipt_number = R1.rec_num);
2370: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R1.rec_num;
2371: /*bug7117223 end*/
2372: IF l_msg_count = 1 Then
2373:
2374: arp_standard.debug('l_msg_data '||l_msg_data);

Line 2428: FROM AR_RECEIPTS_GT

2424: DECLARE
2425:
2426: CURSOR C_REC3 IS
2427: SELECT paying_site_use_id,payment_instrument
2428: FROM AR_RECEIPTS_GT
2429: WHERE PAYMENT_SCHEDULE_ID is not null
2430: Group by paying_site_use_id,payment_instrument;
2431:
2432: BEGIN

Line 2490: UPDATE AR_RECEIPTS_GT

2486: THEN
2487: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
2488:
2489:
2490: UPDATE AR_RECEIPTS_GT
2491: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
2492: WHERE paying_site_use_id = cust3.paying_site_use_id
2493: AND payment_instrument = cust3.payment_instrument;
2494:

Line 2541: from AR_RECEIPTS_GT

2537: select receipt_number rec_num,
2538: paying_customer_id pay_cust_id,
2539: paying_site_use_id pay_site_id,
2540: sum(amount_due_remaining) amt
2541: from AR_RECEIPTS_GT
2542: group by receipt_number,paying_customer_id,paying_site_use_id;
2543:
2544: /* calling create cash */
2545:

Line 2580: from AR_RECEIPTS_GT

2576:
2577: DECLARE
2578: cursor c3_inv is
2579: select payment_schedule_id
2580: from AR_RECEIPTS_GT
2581: where receipt_number = R3.rec_num;
2582: BEGIN
2583: FOR R3_inv in c3_inv loop
2584: insert_exceptions(

Line 2596: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2592: END LOOP;
2593: END;
2594:
2595: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2596: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2597: where receipt_number = R3.rec_num);
2598: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;
2599: END IF;
2600:

Line 2598: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;

2594:
2595: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2596: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2597: where receipt_number = R3.rec_num);
2598: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;
2599: END IF;
2600:
2601: EXCEPTION
2602: WHEN NO_DATA_FOUND THEN -- NO_DATA_FOUND Condition as per Bug:5346610

Line 2612: from ar_receipts_gt

2608:
2609: IF l_err_code <> 'ARZCAR_CUST_MIN_AMT' OR l_err_code IS NULL THEN -- Condition as per Bug:5346610
2610: select payment_trxn_extension_id
2611: into l_payment_trxn_extension_id
2612: from ar_receipts_gt
2613: where receipt_number = R3.rec_num
2614: and paying_customer_id = R3.pay_cust_id
2615: and paying_site_use_id = R3.pay_site_id
2616: and rownum = 1;

Line 2652: Update ar_receipts_gt set cash_receipt_id = l_cr_id

2648: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
2649: arp_standard.debug('x_return_status: '||l_return_status);
2650:
2651: IF l_return_status = 'S' THEN
2652: Update ar_receipts_gt set cash_receipt_id = l_cr_id
2653: where receipt_number = R3.rec_num;
2654: END IF;
2655: IF l_return_status <> 'S' THEN
2656: /*bug7117223 start*/

Line 2658: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2654: END IF;
2655: IF l_return_status <> 'S' THEN
2656: /*bug7117223 start*/
2657: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2658: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2659: where receipt_number = R3.rec_num);
2660: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;
2661: /*bug7117223 end*/
2662: IF l_msg_count = 1 Then

Line 2660: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;

2656: /*bug7117223 start*/
2657: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2658: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2659: where receipt_number = R3.rec_num);
2660: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R3.rec_num;
2661: /*bug7117223 end*/
2662: IF l_msg_count = 1 Then
2663:
2664: arp_standard.debug('l_msg_data '||l_msg_data);

Line 2720: FROM ar_receipts_gt

2716: DECLARE
2717:
2718: CURSOR C_REC4 IS
2719: SELECT paying_site_use_id ,due_date,payment_instrument
2720: FROM ar_receipts_gt
2721: WHERE payment_schedule_id is not null
2722: Group by paying_site_use_id,due_date,payment_instrument;
2723:
2724: BEGIN

Line 2779: UPDATE AR_RECEIPTS_GT

2775: THEN
2776: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
2777:
2778:
2779: UPDATE AR_RECEIPTS_GT
2780: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
2781: WHERE due_date = cust4.due_date
2782: AND payment_instrument = cust4.payment_instrument
2783: AND paying_site_use_id = cust4.paying_site_use_id;

Line 2828: from ar_receipts_gt

2824: paying_customer_id pay_cust_id,
2825: paying_site_use_id pay_site_id,
2826: due_date,
2827: sum(amount_due_remaining) amt
2828: from ar_receipts_gt
2829: group by receipt_number,paying_customer_id,paying_site_use_id,due_date;
2830:
2831: /* calling create cash */
2832:

Line 2867: from AR_RECEIPTS_GT

2863:
2864: DECLARE
2865: cursor c4_inv is
2866: select payment_schedule_id
2867: from AR_RECEIPTS_GT
2868: where receipt_number = R4.rec_num;
2869: BEGIN
2870: FOR R4_inv in c4_inv loop
2871: insert_exceptions(

Line 2883: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2879: END LOOP;
2880: END;
2881:
2882: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2883: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2884: where receipt_number = R4.rec_num);
2885: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;
2886: END IF;
2887:

Line 2885: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;

2881:
2882: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2883: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2884: where receipt_number = R4.rec_num);
2885: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;
2886: END IF;
2887:
2888: EXCEPTION
2889: WHEN NO_DATA_FOUND THEN -- NO_DATA_FOUND Condition as per Bug:5346610

Line 2900: from ar_receipts_gt

2896: IF l_err_code <> 'ARZCAR_CUST_MIN_AMT' OR l_err_code IS NULL THEN --Condition as per Bug:5346610
2897:
2898: select payment_trxn_extension_id
2899: into l_payment_trxn_extension_id
2900: from ar_receipts_gt
2901: where receipt_number = R4.rec_num
2902: and paying_customer_id = R4.pay_cust_id
2903: and paying_site_use_id = R4.pay_site_id
2904: and due_date = R4.due_date

Line 2942: Update ar_receipts_gt set cash_receipt_id = l_cr_id

2938: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
2939: arp_standard.debug('x_return_status: '||l_return_status);
2940:
2941: IF l_return_status = 'S' THEN
2942: Update ar_receipts_gt set cash_receipt_id = l_cr_id
2943: where receipt_number = R4.rec_num;
2944: END IF;
2945: IF l_return_status <> 'S' THEN
2946: /*bug7117223 start*/

Line 2948: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

2944: END IF;
2945: IF l_return_status <> 'S' THEN
2946: /*bug7117223 start*/
2947: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2948: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2949: where receipt_number = R4.rec_num);
2950: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;
2951: /*bug7117223 end*/
2952: IF l_msg_count = 1 Then

Line 2950: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;

2946: /*bug7117223 start*/
2947: Update ar_payment_schedules set selected_for_receipt_batch_id = null
2948: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
2949: where receipt_number = R4.rec_num);
2950: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R4.rec_num;
2951: /*bug7117223 end*/
2952: IF l_msg_count = 1 Then
2953:
2954: arp_standard.debug('l_msg_data '||l_msg_data);

Line 3013: FROM ar_receipts_gt

3009: DECLARE
3010:
3011: CURSOR C_REC5 IS
3012: SELECT paying_customer_id ,due_date,payment_instrument
3013: FROM ar_receipts_gt
3014: WHERE payment_schedule_id is not null
3015: Group by paying_customer_id ,due_date,payment_instrument;
3016:
3017: BEGIN

Line 3073: UPDATE AR_RECEIPTS_GT

3069: THEN
3070: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
3071:
3072:
3073: UPDATE AR_RECEIPTS_GT
3074: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
3075: WHERE due_date = cust5.due_date
3076: AND payment_instrument = cust5.payment_instrument;
3077:

Line 3118: from ar_receipts_gt

3114: select receipt_number rec_num,
3115: paying_customer_id pay_cust_id,
3116: due_date,
3117: sum(amount_due_remaining) amt
3118: from ar_receipts_gt
3119: group by receipt_number,paying_customer_id,due_date;
3120:
3121: /* calling create cash */
3122:

Line 3157: from AR_RECEIPTS_GT

3153:
3154: DECLARE
3155: cursor c5_inv is
3156: select payment_schedule_id
3157: from AR_RECEIPTS_GT
3158: where receipt_number = R5.rec_num;
3159: BEGIN
3160: FOR R5_inv in c5_inv loop
3161: insert_exceptions(

Line 3172: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3168: );
3169: END LOOP;
3170: END;
3171: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3172: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3173: where receipt_number = R5.rec_num);
3174: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;
3175: END IF;
3176:

Line 3174: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;

3170: END;
3171: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3172: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3173: where receipt_number = R5.rec_num);
3174: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;
3175: END IF;
3176:
3177:
3178: EXCEPTION

Line 3189: from ar_receipts_gt

3185:
3186: IF l_err_code <> 'ARZCAR_CUST_MIN_AMT' OR l_err_code IS NULL THEN --Condition as per Bug:5346610
3187: select payment_trxn_extension_id
3188: into l_payment_trxn_extension_id
3189: from ar_receipts_gt
3190: where receipt_number = R5.rec_num
3191: and paying_customer_id = R5.pay_cust_id
3192: and due_date = R5.due_date
3193: and rownum = 1;

Line 3229: Update ar_receipts_gt set cash_receipt_id = l_cr_id

3225: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
3226: arp_standard.debug('x_return_status: '||l_return_status);
3227:
3228: IF l_return_status = 'S' THEN
3229: Update ar_receipts_gt set cash_receipt_id = l_cr_id
3230: where receipt_number = R5.rec_num;
3231: END IF;
3232: IF l_return_status <> 'S' THEN
3233: /*bug7117223 start*/

Line 3235: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3231: END IF;
3232: IF l_return_status <> 'S' THEN
3233: /*bug7117223 start*/
3234: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3235: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3236: where receipt_number = R5.rec_num);
3237: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;
3238: /*bug7117223 end*/
3239: IF l_msg_count = 1 Then

Line 3237: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;

3233: /*bug7117223 start*/
3234: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3235: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3236: where receipt_number = R5.rec_num);
3237: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R5.rec_num;
3238: /*bug7117223 end*/
3239: IF l_msg_count = 1 Then
3240:
3241: arp_standard.debug('l_msg_data '||l_msg_data);

Line 3300: from ar_receipts_gt where payment_schedule_id is not null

3296: DECLARE
3297:
3298: CURSOR C_REC6 IS
3299: select payment_channel_code,paying_customer_id,payment_trxn_extension_id
3300: from ar_receipts_gt where payment_schedule_id is not null
3301: group by payment_channel_code, paying_customer_id,payment_trxn_extension_id;
3302:
3303:
3304: BEGIN

Line 3360: UPDATE ar_receipts_gt

3356: IF (l_doc_sequence_value is not NULL)
3357: THEN
3358: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
3359:
3360: UPDATE ar_receipts_gt
3361: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
3362: WHERE payment_channel_code = cust6.payment_channel_code
3363: and payment_trxn_extension_id = cust6.payment_trxn_extension_id;
3364:

Line 3406: from ar_receipts_gt

3402: select receipt_number rec_num,
3403: paying_customer_id pay_cust_id,
3404: payment_trxn_extension_id pmt_trxn_ext_id,
3405: sum(amount_due_remaining) amt
3406: from ar_receipts_gt
3407: group by receipt_number,paying_customer_id,customer_bank_account_id,payment_trxn_extension_id;
3408:
3409: /* calling create cash */
3410:

Line 3442: from AR_RECEIPTS_GT

3438:
3439: DECLARE
3440: cursor c6_inv is
3441: select payment_schedule_id
3442: from AR_RECEIPTS_GT
3443: where receipt_number = R6.rec_num;
3444: BEGIN
3445: FOR R6_inv in c6_inv loop
3446: insert_exceptions(

Line 3457: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3453: );
3454: END LOOP;
3455: END;
3456: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3457: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3458: where receipt_number = R6.rec_num);
3459: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;
3460: END IF;
3461:

Line 3459: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;

3455: END;
3456: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3457: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3458: where receipt_number = R6.rec_num);
3459: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;
3460: END IF;
3461:
3462: EXCEPTION
3463: WHEN NO_DATA_FOUND THEN -- NO_DATA_FOUND Condition as per Bug:5346610

Line 3501: Update ar_receipts_gt set cash_receipt_id = l_cr_id

3497: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
3498: arp_standard.debug('x_return_status: '||l_return_status);
3499:
3500: IF l_return_status = 'S' THEN
3501: Update ar_receipts_gt set cash_receipt_id = l_cr_id
3502: where receipt_number = R6.rec_num;
3503: END IF;
3504: IF l_return_status <> 'S' THEN
3505: /*bug7117223 start*/

Line 3507: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3503: END IF;
3504: IF l_return_status <> 'S' THEN
3505: /*bug7117223 start*/
3506: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3507: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3508: where receipt_number = R6.rec_num);
3509: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;
3510: /*bug7117223 end*/
3511: IF l_msg_count = 1 Then

Line 3509: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;

3505: /*bug7117223 start*/
3506: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3507: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3508: where receipt_number = R6.rec_num);
3509: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R6.rec_num;
3510: /*bug7117223 end*/
3511: IF l_msg_count = 1 Then
3512:
3513: arp_standard.debug('l_msg_data '||l_msg_data);

Line 3566: from ar_receipts_gt where payment_schedule_id is not null

3562: DECLARE
3563:
3564: CURSOR C_REC7 IS
3565: select payment_instrument,paying_customer_id,payment_trxn_extension_id
3566: from ar_receipts_gt where payment_schedule_id is not null
3567: group by payment_instrument,paying_customer_id,payment_trxn_extension_id;
3568:
3569:
3570: BEGIN

Line 3626: UPDATE ar_receipts_gt

3622: IF (l_doc_sequence_value is not NULL)
3623: THEN
3624: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
3625:
3626: UPDATE ar_receipts_gt
3627: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
3628: WHERE payment_instrument = cust7.payment_instrument
3629: and payment_trxn_extension_id = cust7.payment_trxn_extension_id;
3630:

Line 3672: from ar_receipts_gt

3668: select receipt_number rec_num,
3669: paying_customer_id pay_cust_id,
3670: payment_trxn_extension_id pmt_trxn_ext_id,
3671: sum(amount_due_remaining) amt
3672: from ar_receipts_gt
3673: group by receipt_number,paying_customer_id,payment_trxn_extension_id;
3674:
3675: /* calling create cash */
3676:

Line 3707: from AR_RECEIPTS_GT

3703:
3704: DECLARE
3705: cursor c7_inv is
3706: select payment_schedule_id
3707: from AR_RECEIPTS_GT
3708: where receipt_number = R7.rec_num;
3709: BEGIN
3710: FOR R7_inv in c7_inv loop
3711: insert_exceptions(

Line 3722: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3718: );
3719: END LOOP;
3720: END;
3721: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3722: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3723: where receipt_number = R7.rec_num);
3724: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;
3725: END IF;
3726:

Line 3724: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;

3720: END;
3721: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3722: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3723: where receipt_number = R7.rec_num);
3724: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;
3725: END IF;
3726:
3727: EXCEPTION
3728: WHEN NO_DATA_FOUND THEN -- NO_DATA_FOUND Condition as per Bug:5346610

Line 3766: Update ar_receipts_gt set cash_receipt_id = l_cr_id

3762: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
3763: arp_standard.debug('x_return_status: '||l_return_status);
3764:
3765: IF l_return_status = 'S' THEN
3766: Update ar_receipts_gt set cash_receipt_id = l_cr_id
3767: where receipt_number = R7.rec_num;
3768: END IF;
3769: IF l_return_status <> 'S' THEN
3770: /*bug7117223 start*/

Line 3772: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3768: END IF;
3769: IF l_return_status <> 'S' THEN
3770: /*bug7117223 start*/
3771: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3772: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3773: where receipt_number = R7.rec_num);
3774: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;
3775: /*bug7117223 end*/
3776: IF l_msg_count = 1 Then

Line 3774: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;

3770: /*bug7117223 start*/
3771: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3772: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3773: where receipt_number = R7.rec_num);
3774: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R7.rec_num;
3775: /*bug7117223 end*/
3776: IF l_msg_count = 1 Then
3777:
3778: arp_standard.debug('l_msg_data '||l_msg_data);

Line 3831: from ar_receipts_gt where payment_schedule_id is not null

3827: DECLARE
3828:
3829: CURSOR C_REC8 IS
3830: select authorization_id,paying_customer_id,payment_trxn_extension_id
3831: from ar_receipts_gt where payment_schedule_id is not null
3832: group by authorization_id,paying_customer_id,payment_trxn_extension_id;
3833:
3834:
3835: BEGIN

Line 3891: UPDATE ar_receipts_gt

3887: IF (l_doc_sequence_value is not NULL)
3888: THEN
3889: arp_standard.debug ('SUCCESS ' || l_doc_sequence_value);
3890:
3891: UPDATE ar_receipts_gt
3892: SET RECEIPT_NUMBER = p_receipt_method_id||'-'||l_doc_sequence_value
3893: WHERE authorization_id = cust8.authorization_id
3894: and payment_trxn_extension_id = cust8.payment_trxn_extension_id;
3895:

Line 3937: from ar_receipts_gt

3933: select receipt_number rec_num,
3934: paying_customer_id pay_cust_id,
3935: payment_trxn_extension_id pmt_trxn_ext_id,
3936: sum(amount_due_remaining) amt
3937: from ar_receipts_gt
3938: group by receipt_number,paying_customer_id,payment_trxn_extension_id;
3939:
3940: /* calling create cash */
3941:

Line 3973: from AR_RECEIPTS_GT

3969:
3970: DECLARE
3971: cursor c8_inv is
3972: select payment_schedule_id
3973: from AR_RECEIPTS_GT
3974: where receipt_number = R8.rec_num;
3975: BEGIN
3976: FOR R8_inv in c8_inv loop
3977: insert_exceptions(

Line 3988: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

3984: );
3985: END LOOP;
3986: END;
3987: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3988: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3989: where receipt_number = R8.rec_num);
3990: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;
3991: END IF;
3992:

Line 3990: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;

3986: END;
3987: Update ar_payment_schedules set selected_for_receipt_batch_id = null
3988: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
3989: where receipt_number = R8.rec_num);
3990: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;
3991: END IF;
3992:
3993: EXCEPTION
3994: WHEN NO_DATA_FOUND THEN -- NO_DATA_FOUND Condition as per Bug:5346610

Line 4031: Update ar_receipts_gt set cash_receipt_id = l_cr_id

4027: arp_standard.debug('x_cash_receipt_id: '||l_cr_id);
4028: arp_standard.debug('x_return_status: '||l_return_status);
4029:
4030: IF l_return_status = 'S' THEN
4031: Update ar_receipts_gt set cash_receipt_id = l_cr_id
4032: where receipt_number = R8.rec_num;
4033: END IF;
4034: IF l_return_status <> 'S' THEN
4035: /*bug7117223 start*/

Line 4037: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt

4033: END IF;
4034: IF l_return_status <> 'S' THEN
4035: /*bug7117223 start*/
4036: Update ar_payment_schedules set selected_for_receipt_batch_id = null
4037: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
4038: where receipt_number = R8.rec_num);
4039: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;
4040: /*bug7117223 end*/
4041: IF l_msg_count = 1 Then

Line 4039: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;

4035: /*bug7117223 start*/
4036: Update ar_payment_schedules set selected_for_receipt_batch_id = null
4037: where payment_schedule_id in (select payment_schedule_id from ar_receipts_gt
4038: where receipt_number = R8.rec_num);
4039: Update ar_receipts_gt set payment_schedule_id = -99999 where receipt_number = R8.rec_num;
4040: /*bug7117223 end*/
4041: IF l_msg_count = 1 Then
4042:
4043: arp_standard.debug('l_msg_data '||l_msg_data);

Line 4387: ar_receipts_gt r,

4383: program_update_date = sysdate
4384: WHERE customer_trx_id in (
4385: SELECT r.customer_trx_id
4386: FROM ar_cash_receipts cr,
4387: ar_receipts_gt r,
4388: ar_cash_receipt_history crh,
4389: iby_trxn_extensions_v trxn_ext
4390: WHERE cr.request_id = l_request_id
4391: AND r.cash_receipt_id = cr.cash_receipt_id

Line 4496: ar_receipts_gt r,

4492: cc_error_text = null
4493: WHERE customer_trx_id in (
4494: SELECT r.customer_trx_id
4495: FROM ar_cash_receipts cr,
4496: ar_receipts_gt r,
4497: ar_cash_receipt_history crh,
4498: iby_trxn_extensions_v trxn_ext
4499: WHERE cr.request_id = l_request_id
4500: AND r.cash_receipt_id = cr.cash_receipt_id

Line 4561: ar_receipts_gt r

4557: r.receipt_number rec_num,
4558: r.cash_receipt_id rec_id
4559: from ar_payment_schedules ps,
4560: ra_customer_trx trx,
4561: ar_receipts_gt r
4562: where trx.customer_trx_id = ps.customer_trx_id
4563: and trx.cc_error_flag = 'Y'
4564: and r.payment_schedule_id = ps.payment_schedule_id;
4565: