[Home] [Help]
22: p_process_status OUT NOCOPY VARCHAR2,
23: p_process_message OUT NOCOPY VARCHAR2)
24: IS
25: BEGIN
26: p_process_status := jai_constants.successful;
27:
28: FOR i in 1..13 LOOP
29: p_error_table(i).type_of_error := 'Common';
30: END LOOP;
95: p_error_table(42).error_description := 'Amount_credited <> SUM(amount_applied) of ar_receivable_applications';
96:
97: EXCEPTION
98: WHEN OTHERS THEN
99: p_process_status := jai_constants.unexpected_error;
100: p_process_message := SUBSTR(SQLERRM,1,300);
101: END populate_error_table;
102:
103:
153: AND rtl.term_id = p_term_id
154: AND rtl.sequence_num = p_terms_sequence_number;
155:
156: BEGIN
157: p_process_status := jai_constants.successful;
158: p_process_message := NULL;
159:
160: /*
161: Process the records only for an invoice not for any other type.
175: END IF;
176:
177: EXCEPTION
178: WHEN OTHERS THEN
179: p_process_status := jai_constants.unexpected_error;
180: p_process_message := SUBSTR(SQLERRM,1,300);
181: END calc_term_apportion_ratio;
182:
183: PROCEDURE rectify_ar_pay_sch(
195: lv_process_message VARCHAR2(2000);
196: lv_sql_statement VARCHAR2(4000);
197:
198: BEGIN
199: p_process_status := jai_constants.successful;
200: p_process_message := NULL;
201:
202: FOR i IN( SELECT *
203: FROM ar_payment_schedules_all
206: LOOP
207:
208: ln_apportion_ratio := null;
209: lv_first_installment_code := null;
210: lv_process_status := jai_constants.successful;
211: lv_process_message := null;
212:
213: calc_term_apportion_ratio(p_invoice_type => i.class ,
214: p_term_id => i.term_id ,
217: p_first_installment_code => lv_first_installment_code ,
218: p_process_status => lv_process_status ,
219: p_process_message => lv_process_message
220: );
221: IF lv_process_status <> jai_constants.successful THEN
222: p_process_status := lv_process_status;
223: p_process_message := lv_process_message;
224: EXIT;
225: END IF;
296: END LOOP;
297:
298: EXCEPTION
299: WHEN OTHERS THEN
300: p_process_status := jai_constants.unexpected_error;
301: p_process_message := SUBSTR(SQLERRM,1,300);
302: END rectify_ar_pay_sch;
303:
304: PROCEDURE rectify_ar_rec_appl(
317: lv_process_message VARCHAR2(2000);
318: lv_sql_statement VARCHAR2(4000);
319:
320: BEGIN
321: p_process_status := jai_constants.successful;
322: p_process_message := NULL;
323:
324: FOR i IN( SELECT *
325: FROM ar_payment_schedules_all
335: p_process_status => lv_process_status ,
336: p_process_message => lv_process_message
337: );
338:
339: IF lv_process_status <> jai_constants.successful THEN
340: p_process_status := lv_process_status;
341: p_process_message := lv_process_message;
342: EXIT;
343: END IF;
437: END LOOP;
438:
439: EXCEPTION
440: WHEN OTHERS THEN
441: p_process_status := jai_constants.unexpected_error;
442: p_process_message := SUBSTR(SQLERRM,1,300);
443: END rectify_ar_rec_appl;
444:
445:
454: ar_vat_tax_all avtl
455: WHERE rctl.vat_tax_id = avtl.vat_tax_id
456: AND rctl.org_id = avtl.org_id
457: AND rctl.customer_trx_id = cp_customer_trx_id
458: AND avtl.tax_code <> jai_constants.tax_code_localization--'Localization' --Added by Bgowrava for Bug#5484865
459: AND rctl.org_id = rctl.org_id
460: AND rctl.line_type IN ('TAX','FREIGHT') ;
461:
462: CURSOR cur_revrec_run(cp_customer_trx_id ra_customer_trx_all.customer_trx_id%TYPE)
496: ln_exists NUMBER;
497: ln_cnt NUMBER; --added for bug#7445602
498:
499: BEGIN
500: p_process_status := jai_constants.successful;
501: p_process_message := NULL;
502:
503: OPEN cur_chk_non_il_taxes(p_customer_trx_id);
504: FETCH cur_chk_non_il_taxes INTO ln_exists;
504: FETCH cur_chk_non_il_taxes INTO ln_exists;
505: CLOSE cur_chk_non_il_taxes;
506:
507: IF ln_exists = 1 THEN
508: p_process_status := jai_constants.expected_error;
509: p_process_message := 'Invoice lines have taxes other than localization type of tax. Please delete it and reprocess the invoice';
510: goto EXIT_POINT;
511: END IF;
512:
521: CLOSE cur_chk_revrec_run_cm;
522: --bug#7445602,end
523:
524: IF ln_cnt = 0 THEN --added the IF condition for bug#7445602
525: p_process_status := jai_constants.expected_error;
526: p_process_message := 'Invoice has already been revenue recognised. Taxes related to invoice cannot be processed';
527: goto EXIT_POINT;
528: END IF;
529: END IF;
534: FETCH cur_chk_gl_posting INTO ln_exists ;
535: CLOSE cur_chk_gl_posting;
536:
537: IF ln_exists = 1 THEN
538: p_process_status := jai_constants.expected_error;
539: p_process_message := 'Invoice has already been GL posted. Taxes related to this invoice cannot be processed';
540: goto EXIT_POINT;
541: END IF;
542:
544: NULL;
545:
546: EXCEPTION
547: WHEN OTHERS THEN
548: p_process_status := jai_constants.unexpected_error;
549: p_process_message := 'For Customer trx id - '||p_customer_trx_id||SUBSTR(SQLERRM,1,300);
550: END pre_validation;
551:
552: PROCEDURE post_validation(p_start_date IN DATE DEFAULT NULL,
834:
835: lt_error_table jai_ar_validate_data_pkg.t_error_table;
836:
837: BEGIN
838: p_process_status := jai_constants.successful;
839: p_process_message := NULL;
840:
841: lv_validate_first := p_validate_first;
842: lv_validate_all := p_validate_all;
849:
850: --Check to insure that either the process runs for a particular customer_trx_id OR start_date and end_date should be specified
851: IF p_customer_trx_id IS NULL THEN
852: IF p_start_date IS NULL OR p_end_date IS NULL THEN
853: p_process_status := jai_constants.expected_error;
854: p_process_message := 'Problem in call to procedure post_validation. If customer_trx_id is not given, then both start_date and end_date are required';
855:
856: GOTO exit_point;
857: END IF;
857: END IF;
858: END IF;
859:
860: If lv_validate_first = 'Y' AND lv_validate_all = 'Y' THEN
861: p_process_status := jai_constants.expected_error;
862: p_process_message := 'Problem in call to procedure post_validation. Only one of these parameter - p_validate_first, p_validate_all should be Y';
863:
864: GOTO exit_point;
865: ELSIF lv_validate_first = 'N' AND lv_validate_all = 'N' THEN
862: p_process_message := 'Problem in call to procedure post_validation. Only one of these parameter - p_validate_first, p_validate_all should be Y';
863:
864: GOTO exit_point;
865: ELSIF lv_validate_first = 'N' AND lv_validate_all = 'N' THEN
866: p_process_status := jai_constants.expected_error;
867: p_process_message := 'Problem in call to procedure post_validation. Only one of these parameter - p_validate_first, p_validate_all should be N';
868:
869: GOTO exit_point;
870: END IF;
880: lv_generate_detail_log := 'Y';
881: END IF;
882:
883: If lv_commit = 'Y' AND lv_fix_data = 'N' THEN
884: p_process_status := jai_constants.expected_error;
885: p_process_message := 'Problem in call to procedure post_validation. p_commit can be Y only if fix_data = Y';
886:
887: GOTO exit_point;
888: NULL;
938: populate_error_table( p_error_table => lt_error_table,
939: p_process_status => lv_process_status,
940: p_process_message => lv_process_message);
941:
942: IF lv_process_status <> jai_constants.successful THEN
943: p_process_status := lv_process_status;
944: p_process_message := lv_process_message;
945:
946: GOTO exit_point;
1043: ln_err_num := 1;
1044: --To check if the data in ja_in_ra_cust_trx_tax_lines is in sync with 'TAX'/'FREIGHT' records in ra_customer_trx_lines_all
1045: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1046: IF rec_il_tax_amount.customer_trx_line_id IS NOT NULL THEN
1047: lv_process_status := jai_constants.expected_error;
1048: lv_process_message := lt_error_table(ln_err_num).error_description;
1049:
1050: if lv_validate_first = 'Y' THEN
1051: goto EXIT_POINT;
1097: --To check if the data is correct in ja_in_ra_cust_trx_tax_lines corresponding to records in ra_cust_trx_line_gl_dist_all
1098: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1099: IF rec_cust_trx_gl_dist_tax.customer_trx_line_id IS NOT NULL THEN
1100:
1101: lv_process_status := jai_constants.expected_error;
1102: lv_process_message := lt_error_table(ln_err_num).error_description;
1103:
1104: if lv_validate_first = 'Y' THEN
1105: goto EXIT_POINT;
1212:
1213: --Check that ra_customer_Trx_lines_all is in sync with gl_dist
1214: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1215: IF rec_chk_gl_dist_rctl.customer_trx_line_id IS NOT NULL THEN
1216: lv_process_status := jai_constants.expected_error;
1217: lv_process_message := lt_error_table(ln_err_num).error_description;
1218:
1219: if lv_validate_first = 'Y' THEN
1220: goto EXIT_POINT;
1264: ln_err_num := 3;
1265: --To check if ADO in ar_payment_schedules_all is equal to amount of REC in ra_cust_trx_line_gl_dist_all
1266: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1267: IF rec_tot_payment_schedule.amount_due_original <> rec_tot_cust_trx_gl_dist_rec.amount THEN
1268: lv_process_status := jai_constants.expected_error;
1269: lv_process_message := lt_error_table(ln_err_num).error_description;
1270:
1271: if lv_validate_first = 'Y' THEN
1272: goto EXIT_POINT;
1303: ln_err_num := 4;
1304: --To check if TO in ar_payment_schedules_all is equal to amount of TAX in ra_cust_trx_line_gl_dist_all
1305: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1306: IF rec_tot_payment_schedule.tax_original <> rec_tot_cust_trx_gl_dist_tax.amount THEN
1307: lv_process_status := jai_constants.expected_error;
1308: lv_process_message := lt_error_table(ln_err_num).error_description;
1309:
1310: if lv_validate_first = 'Y' THEN
1311: goto EXIT_POINT;
1342: ln_err_num := 5;
1343: --To check if FO in ar_payment_schedules_all is equal to amount of FREIGHT in ra_cust_trx_line_gl_dist_all
1344: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1345: IF rec_tot_payment_schedule.freight_original <> rec_tot_cust_trx_gl_dist_frt.amount THEN
1346: lv_process_status := jai_constants.expected_error;
1347: lv_process_message := lt_error_table(ln_err_num).error_description;
1348:
1349: if lv_validate_first = 'Y' THEN
1350: goto EXIT_POINT;
1384: IF rec_tot_payment_schedule.amount_due_original <> ( rec_tot_payment_schedule.amount_line_items_original
1385: + rec_tot_payment_schedule.tax_original
1386: + rec_tot_payment_schedule.freight_original)
1387: THEN
1388: lv_process_status := jai_constants.expected_error;
1389: lv_process_message := lt_error_table(ln_err_num).error_description;
1390:
1391: if lv_validate_first = 'Y' THEN
1392: goto EXIT_POINT;
1410: IF rec_tot_payment_schedule.amount_due_remaining <> ( rec_tot_payment_schedule.amount_line_items_remaining
1411: + rec_tot_payment_schedule.tax_remaining
1412: + rec_tot_payment_schedule.freight_remaining)
1413: THEN
1414: lv_process_status := jai_constants.expected_error;
1415: lv_process_message := lt_error_table(ln_err_num).error_description;
1416:
1417: if lv_validate_first = 'Y' THEN
1418: goto EXIT_POINT;
1444: rec_tot_payment_schedule.acctd_amount_due_remaining <> 0 OR
1445: rec_tot_payment_schedule.tax_remaining <> 0 OR
1446: rec_tot_payment_schedule.freight_remaining <> 0 THEN
1447:
1448: lv_process_status := jai_constants.expected_error;
1449: lv_process_message := lt_error_table(ln_err_num).error_description;
1450:
1451: if lv_validate_first = 'Y' THEN
1452: goto EXIT_POINT;
1503: ln_err_num := 22;
1504: --To check, if ADO = amount applied
1505: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1506: IF rec_tot_payment_schedule.amount_due_original <> rec_tot_payment_schedule.amount_applied THEN
1507: lv_process_status := jai_constants.expected_error;
1508: lv_process_message := lt_error_table(ln_err_num).error_description;
1509:
1510: if lv_validate_first = 'Y' THEN
1511: goto EXIT_POINT;
1564: IF ABS(rec_tot_payment_schedule.amount_due_original) <> ( ABS(rec_cm_ar_recv_appl.line_applied)
1565: + ABS(rec_cm_ar_recv_appl.tax_applied)
1566: + ABS(rec_cm_ar_recv_appl.freight_applied))
1567: THEN
1568: lv_process_status := jai_constants.expected_error;
1569: lv_process_message := lt_error_table(ln_err_num).error_description;
1570:
1571: if lv_validate_first = 'Y' THEN
1572: goto EXIT_POINT;
1613: IF rec_cm_ar_recv_appl.amount_applied <> ( rec_cm_ar_recv_appl.line_applied
1614: + rec_cm_ar_recv_appl.tax_applied
1615: + rec_cm_ar_recv_appl.freight_applied)
1616: THEN
1617: lv_process_status := jai_constants.expected_error;
1618: lv_process_message := lt_error_table(ln_err_num).error_description;
1619:
1620: if lv_validate_first = 'Y' THEN
1621: goto EXIT_POINT;
1669: --To check, if AAAF = AA * exchange_rate(CM) for ar_receivable_applications_all
1670: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1671: IF ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_from,0) <> ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_from_calc,0)
1672: THEN
1673: lv_process_status := jai_constants.expected_error;
1674: lv_process_message := lt_error_table(ln_err_num).error_description;
1675:
1676: if lv_validate_first = 'Y' THEN
1677: goto EXIT_POINT;
1729: --To check, if AAAT = AA * exchange_rate(INV) for ar_receivable_applications_all
1730: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1731: IF ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_to,0) <> ROUND(rec_cm_ar_recv_appl.acctd_amount_applied_to_calc,0)
1732: THEN
1733: lv_process_status := jai_constants.expected_error;
1734: lv_process_message := lt_error_table(ln_err_num).error_description;
1735:
1736: if lv_validate_first = 'Y' THEN
1737: goto EXIT_POINT;
1810: + rec_inv_cash_cm_recv_appl.line_applied
1811: + rec_inv_cash_cm_recv_appl.tax_applied
1812: + rec_inv_cash_cm_recv_appl.freight_applied)
1813: THEN
1814: lv_process_status := jai_constants.expected_error;
1815: lv_process_message := lt_error_table(ln_err_num).error_description;
1816:
1817: if lv_validate_first = 'Y' THEN
1818: goto EXIT_POINT;
1932: --To check, if amount_credited = SUM(amount_applied) of ar_receivable_applications
1933: IF lt_error_table(ln_err_num).enable = 'Y' THEN
1934: IF ABS(rec_tot_payment_schedule.amount_credited) <> ABS(rec_inv_ar_recv_appl.amount_applied)
1935: THEN
1936: lv_process_status := jai_constants.expected_error;
1937: lv_process_message := lt_error_table(ln_err_num).error_description;
1938:
1939: if lv_validate_first = 'Y' THEN
1940: goto EXIT_POINT;
2026: LOOP
2027: IF ROUND(rec_payment_schedules.acctd_amount_due_remaining)
2028: <> ROUND(rec_payment_schedules.amount_due_remaining * rec_payment_schedules.exchange_rate)
2029: THEN
2030: lv_process_status := jai_constants.expected_error;
2031: lv_process_message := lt_error_table(ln_err_num).error_description;
2032:
2033: ln_local_cnt := ln_local_cnt + 1;
2034:
2088: LOOP
2089: --To check if status = 'CL' and AADR <> 0 and ADR <> 0
2090: IF rec_payment_schedules.status = 'CL' THEN
2091: IF ROUND(rec_payment_schedules.amount_due_remaining) <> 0 OR ROUND(rec_payment_schedules.acctd_amount_due_remaining) <> 0 THEN
2092: lv_process_status := jai_constants.expected_error;
2093: lv_process_message := lt_error_table(ln_err_num).error_description;
2094:
2095: ln_local_cnt := ln_local_cnt + 1;
2096:
2161: LOOP
2162: --To Check if status = 'OP' and gl_date_closed = '31-DEC-4712'
2163: IF rec_payment_schedules.status = 'OP' THEN
2164: IF rec_payment_schedules.gl_date_closed <> TO_DATE('31/12/4712','dd/mm/yyyy') THEN
2165: lv_process_status := jai_constants.expected_error;
2166: lv_process_message := lt_error_table(ln_err_num).error_description;
2167:
2168: ln_local_cnt := ln_local_cnt + 1;
2169:
2231:
2232: IF lt_error_table(ln_err_num).enable = 'Y' THEN
2233: FOR rec_cur_sync_il_line_tax IN cur_sync_il_line_tax( cp_customer_trx_id => rec_get_cust_trx.customer_trx_id)
2234: LOOP
2235: lv_process_status := jai_constants.expected_error;
2236: lv_process_message := lt_error_table(ln_err_num).error_description;
2237:
2238: ln_local_cnt := ln_local_cnt + 1;
2239:
2289:
2290: IF lt_error_table(ln_err_num).enable = 'Y' THEN
2291: FOR rec_sync_il_hdr_tax IN cur_sync_il_hdr_tax(cp_customer_trx_id => rec_get_cust_trx.customer_trx_id)
2292: LOOP
2293: lv_process_status := jai_constants.expected_error;
2294: lv_process_message := lt_error_table(ln_err_num).error_description;
2295:
2296: ln_local_cnt := ln_local_cnt + 1;
2297:
2369: END IF;
2370:
2371: EXCEPTION
2372: WHEN OTHERS THEN
2373: p_process_status := jai_constants.unexpected_error;
2374: p_process_message := SUBSTR(sqlerrm,1,300);
2375:
2376: ROLLBACK to start_program;
2377: