[Home] [Help]
175: AND ((a.amount > 0 and
176: source_trx_type = 'REVERSE_PAID_ACCOUNTING') OR
177: (a.amount < 0 and
178: source_trx_type = 'REVERSE_AVOID_ACCOUNTING'))
179: and source_table_name = jai_constants.ap_payments
180: and source_document_id = a.invoice_payment_id)
181: AND EXISTS
182: (SELECT 1
183: FROM jai_rgm_trx_refs jrtr
180: and source_document_id = a.invoice_payment_id)
181: AND EXISTS
182: (SELECT 1
183: FROM jai_rgm_trx_refs jrtr
184: WHERE jrtr.source = jai_constants.source_ap
185: AND jrtr.invoice_id = aida.invoice_id
186: AND jrtr.line_id = aida.invoice_distribution_id
187:
188: )
226: SELECT aia.invoice_id
227: FROM ap_invoices_all aia, jai_ap_invoice_lines jail
228: WHERE aia.invoice_id = jail.invoice_id
229: AND jail.organization_id = pn_organization_id)
230: AND jrtr.SOURCE = jai_constants.source_ap
231: AND jrtr.invoice_id = ainvd.invoice_id
232: AND apinvp.invoice_id = ainvd.invoice_id
233: AND jrtr.line_id = ainvd.invoice_distribution_id
234: AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ainvd.invoice_distribution_id) = 'Y'
251: ap_invoice_distributions_all ap_dist,
252: jai_rgm_trx_refs jrtr
253: WHERE prepay.invoice_id = cp_invoice_id
254: AND ap_dist.invoice_id = prepay.invoice_id
255: AND prepay.line_type_lookup_code = jai_constants.prepay_line
256: AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
257: AND prepay.accounting_date < cp_start_date
258: AND jrtr.SOURCE = jai_constants.source_ap
259: AND jrtr.invoice_id = ap_dist.invoice_id
254: AND ap_dist.invoice_id = prepay.invoice_id
255: AND prepay.line_type_lookup_code = jai_constants.prepay_line
256: AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
257: AND prepay.accounting_date < cp_start_date
258: AND jrtr.SOURCE = jai_constants.source_ap
259: AND jrtr.invoice_id = ap_dist.invoice_id
260: AND jrtr.line_id = ap_dist.invoice_distribution_id
261: AND NOT EXISTS
262: (select 1
260: AND jrtr.line_id = ap_dist.invoice_distribution_id
261: AND NOT EXISTS
262: (select 1
263: from jai_rgm_trx_records -- CHK is this required
264: where source = jai_constants.source_ap
265: AND ((prepay.amount < 0 AND
266: source_trx_type = 'REVERSE_APPLY_ACCOUNTING') OR
267: (prepay.amount > 0 AND
268: source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'))
265: AND ((prepay.amount < 0 AND
266: source_trx_type = 'REVERSE_APPLY_ACCOUNTING') OR
267: (prepay.amount > 0 AND
268: source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'))
269: and source_table_name = jai_constants.ap_prepayments
270: and source_document_id = prepay.invoice_distribution_id)
271: ORDER BY invoice_distribution_id;
272:
273: CURSOR c_prepayment_applications(cp_start_date IN DATE,
288: jai_rgm_trx_refs jrtr
289: WHERE ap_dist.org_id = p_org_id
290: AND prepay.org_id = p_org_id -- for bug 16071477 by anupgupt
291: AND ap_dist.invoice_id = prepay.invoice_id
292: AND prepay.line_type_lookup_code = jai_constants.prepay_line
293: AND jrtr.SOURCE = jai_constants.source_ap
294: AND jrtr.invoice_id = ap_dist.invoice_id
295: AND jrtr.line_id = ap_dist.invoice_distribution_id
296: AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
289: WHERE ap_dist.org_id = p_org_id
290: AND prepay.org_id = p_org_id -- for bug 16071477 by anupgupt
291: AND ap_dist.invoice_id = prepay.invoice_id
292: AND prepay.line_type_lookup_code = jai_constants.prepay_line
293: AND jrtr.SOURCE = jai_constants.source_ap
294: AND jrtr.invoice_id = ap_dist.invoice_id
295: AND jrtr.line_id = ap_dist.invoice_distribution_id
296: AND jai_cmn_reverse_charge_pkg.is_rvs_chrg_liablity_dist(ap_dist.invoice_distribution_id) = 'Y'
297: /* AND ((cp_start_date IS NULL AND
327: lv_codepath := jai_general_pkg.plot_codepath(1,
328: lv_codepath,
329: 'PROCESS_PAYMENT',
330: 'START');
331: FOR invo IN c_batch_references(pn_batch_id, jai_constants.source_ap) LOOP
332: ln_invoice_id := invo.invoice_id;
333: lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
334: -- Logic to Process the PAST DATED PAYMENTS that are not processed due to Invoice Accounting did not happen
335: FOR inv_payment IN c_previous_payments_of_inv(ln_invoice_id,
358: process_charge_acct(pn_batch_id => pn_batch_id,
359: pn_regime_id => pn_regime_id,
360: pn_organization_id => l_organization_id,
361: pn_location_id => l_location_id,
362: pv_payment_table_name => jai_constants.ap_payments,
363: pn_invoice_id => ln_invoice_id,
364: pn_line_number => inv_payment.invoice_line_number,
365: pn_distribution_id => inv_payment.invoice_distribution_id,
366: pd_date => inv_payment.accounting_date,
365: pn_distribution_id => inv_payment.invoice_distribution_id,
366: pd_date => inv_payment.accounting_date,
367: pn_invoice_payment_id => inv_payment.invoice_payment_id,
368: pn_payment_amount => inv_payment.amount,
369: p_inv_accounting_chk_done => jai_constants.yes,
370: p_process_flag => lv_process_flag,
371: p_process_message => lv_process_message);
372:
373: IF lv_process_flag IN
370: p_process_flag => lv_process_flag,
371: p_process_message => lv_process_message);
372:
373: IF lv_process_flag IN
374: (jai_constants.expected_error, jai_constants.unexpected_error) THEN
375: ROLLBACK TO process_reverse_payment;
376:
377: p_process_flag := lv_process_flag;
378: p_process_message := lv_process_message;
410: pn_regime_id => pn_regime_id,
411: pn_organization_id => l_organization_id,
412: pn_location_id => l_location_id,
413: pn_invoice_id => ln_invoice_id,
414: pv_payment_table_name => jai_constants.ap_prepayments,
415: pn_line_number => ppa.invoice_line_number,
416: pn_distribution_id => ppa.invoice_distribution_id,
417: pd_date => ppa.accounting_date,
418: pn_invoice_payment_id => ppa.prepay_dist_id,
416: pn_distribution_id => ppa.invoice_distribution_id,
417: pd_date => ppa.accounting_date,
418: pn_invoice_payment_id => ppa.prepay_dist_id,
419: pn_payment_amount => ppa.apply_amt,
420: p_inv_accounting_chk_done => jai_constants.yes,
421: p_process_flag => lv_process_flag,
422: p_process_message => lv_process_message);
423:
424: IF lv_process_flag IN
421: p_process_flag => lv_process_flag,
422: p_process_message => lv_process_message);
423:
424: IF lv_process_flag IN
425: (jai_constants.expected_error, jai_constants.unexpected_error) THEN
426: ROLLBACK TO process_reverse_payment;
427: p_process_flag := lv_process_flag;
428: p_process_message := lv_process_message;
429: END IF;
463: pn_regime_id => pn_regime_id,
464: pn_organization_id => l_organization_id,
465: pn_location_id => l_location_id,
466: pn_invoice_id => ln_invoice_id,
467: pv_payment_table_name => jai_constants.ap_payments,
468: pn_line_number => inv_payment.invoice_line_number,
469: pn_distribution_id => inv_payment.invoice_distribution_id,
470: pd_date => inv_payment.accounting_date,
471: pn_invoice_payment_id => inv_payment.invoice_payment_id,
469: pn_distribution_id => inv_payment.invoice_distribution_id,
470: pd_date => inv_payment.accounting_date,
471: pn_invoice_payment_id => inv_payment.invoice_payment_id,
472: pn_payment_amount => inv_payment.amount,
473: p_inv_accounting_chk_done => jai_constants.no,
474: p_process_flag => lv_process_flag,
475: p_process_message => lv_process_message);
476:
477: IF lv_process_flag IN
474: p_process_flag => lv_process_flag,
475: p_process_message => lv_process_message);
476:
477: IF lv_process_flag IN
478: (jai_constants.expected_error, jai_constants.unexpected_error) THEN
479: ROLLBACK TO process_reverse_payment;
480: p_process_flag := lv_process_flag;
481: p_process_message := lv_process_message;
482: END IF;
521: pn_regime_id => pn_regime_id,
522: pn_organization_id => l_organization_id,
523: pn_location_id => l_location_id,
524: pn_invoice_id => ln_invoice_id,
525: pv_payment_table_name => jai_constants.ap_prepayments,
526: pn_line_number => pp.invoice_line_number,
527: pn_distribution_id => pp.invoice_distribution_id,
528: pd_date => pp.accounting_date,
529: pn_invoice_payment_id => pp.prepay_dist_id,
527: pn_distribution_id => pp.invoice_distribution_id,
528: pd_date => pp.accounting_date,
529: pn_invoice_payment_id => pp.prepay_dist_id,
530: pn_payment_amount => pp.apply_amt,
531: p_inv_accounting_chk_done => jai_constants.no,
532: p_process_flag => lv_process_flag,
533: p_process_message => lv_process_message);
534:
535: IF lv_process_flag IN
532: p_process_flag => lv_process_flag,
533: p_process_message => lv_process_message);
534:
535: IF lv_process_flag IN
536: (jai_constants.expected_error, jai_constants.unexpected_error) THEN
537: ROLLBACK TO process_reverse_payment;
538: p_process_flag := lv_process_flag;
539: p_process_message := lv_process_message;
540: END IF;
550: IF g_debug = 'Y' THEN
551: FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
552: END IF;
553:
554: p_process_flag := jai_constants.successful;
555:
556: EXCEPTION
557: WHEN OTHERS THEN
558: ROLLBACK;
555:
556: EXCEPTION
557: WHEN OTHERS THEN
558: ROLLBACK;
559: p_process_flag := jai_constants.unexpected_error;
560: p_process_message := 'Unexpected Error Occured in Process_reverse_charge:' ||
561: SQLERRM;
562: FND_FILE.put_line(fnd_file.log,
563: 'Unexpected Error Occured:' || p_process_message);
786: INTO rec_get_curr_dtls;
787: CLOSE c_get_curr_dtls;
788:
789: -- Accounting check for the invoice_distribution, whether it is accounted or not
790: IF p_inv_accounting_chk_done = jai_constants.no THEN
791: OPEN c_invoice_distribution(pn_distribution_id);
792: FETCH c_invoice_distribution
793: INTO r_dist;
794: CLOSE c_invoice_distribution;
795: r_dist.invoice_amount := lv_total_inv_amount;
796: -- Following condition is true only if Invoice Distribution Accounting did not happen
797: IF r_dist.posted_flag IS NULL OR r_dist.posted_flag <> 'Y' THEN
798:
799: p_process_flag := jai_constants.not_accounted;
800: --p_process_message := 'Invoice is not accounted';
801: RETURN;
802: END IF;
803:
809:
810: --ln_regime_id := pn_regime_id;
811: FOR lr_dist_line IN get_dist_line_cur LOOP
812: lv_codepath := jai_general_pkg.plot_codepath(2, lv_codepath);
813: OPEN c_reference(jai_constants.source_ap,
814: lr_dist_line.invoice_id,
815: lr_dist_line.invoice_distribution_id);
816: FETCH c_reference
817: INTO r_ref;
821: null;
822: ELSE
823:
824:
825: OPEN c_rgm_repository_id(jai_constants.source_ap,
826: pv_payment_table_name,
827: pn_invoice_payment_id,
828: r_ref.reference_id);
829: FETCH c_rgm_repository_id
831: CLOSE c_rgm_repository_id;
832: lv_codepath := jai_general_pkg.plot_codepath(3, lv_codepath);
833: -- if the following is satisfied, then it means the payment against the invoice is already processed
834: IF ln_rgm_reposotory_id IS NOT NULL THEN
835: p_process_flag := jai_constants.already_processed;
836: RETURN;
837: END IF;
838:
839: IF r_dist.invoice_distribution_id IS NULL THEN
844: END IF;
845:
846: --Decide the source transaction type: REVERSE_PAI_ACCOUNTING, REVERSE_AVOID_ACCOUNTING,
847: --REVERSE_APPLY_ACCOUNTING, REVERSE_CHARGE_ACCOUNTING
848: IF pv_payment_table_name = jai_constants.ap_payments THEN
849:
850: lv_codepath := jai_general_pkg.plot_codepath(8, lv_codepath);
851: OPEN c_invoice_payment(pn_invoice_payment_id);
852: FETCH c_invoice_payment
871: IF r_payment.amount > 0 THEN
872: IF r_payment.future_pay_due_date IS NOT NULL THEN
873: lv_codepath := jai_general_pkg.plot_codepath(10,
874: lv_codepath);
875: lv_source_trx_type := jai_constants.payment;
876: ld_transaction_date := r_payment.future_pay_due_date;
877: ELSE
878: lv_codepath := jai_general_pkg.plot_codepath(11,
879: lv_codepath);
890: ld_transaction_date := r_payment.void_date;
891: ELSE
892: lv_codepath := jai_general_pkg.plot_codepath(13,
893: lv_codepath);
894: lv_source_trx_type := jai_constants.payment_reversal;
895: ld_transaction_date := r_payment.check_date;
896: END IF;
897: END IF;
898:
895: ld_transaction_date := r_payment.check_date;
896: END IF;
897: END IF;
898:
899: ELSIF pv_payment_table_name = jai_constants.ap_prepayments THEN
900:
901: lv_codepath := jai_general_pkg.plot_codepath(14, lv_codepath);
902: OPEN c_invoice_distribution(pn_invoice_payment_id);
903: FETCH c_invoice_distribution
927: IF r_dist.reversal_flag = 'Y' AND
928: r_dist.parent_reversal_id IS NOT NULL THEN
929:
930: lv_codepath := jai_general_pkg.plot_codepath(17, lv_codepath);
931: OPEN c_reference(jai_constants.source_ap,
932: pn_invoice_id,
933: r_dist.parent_reversal_id);
934: FETCH c_reference
935: INTO r_parent_ref;
936: CLOSE c_reference;
937:
938: UPDATE jai_rgm_trx_refs
939: SET reversal_flag = 'Y', last_update_date = sysdate
940: WHERE source = jai_constants.source_ap
941: AND invoice_id = pn_invoice_id
942: AND line_id in (pn_distribution_id, r_dist.parent_reversal_id);
943:
944: ln_recovered_amount := -r_parent_ref.recovered_amount;
944: ln_recovered_amount := -r_parent_ref.recovered_amount;
945: ln_discounted_amount := -r_parent_ref.discounted_amount;
946:
947: -- following elsif is added to take care of void scenarios, where in the recovered amt againt the main payment is reversed
948: ELSIF lv_source_trx_type = 'REVERSE_AVOID_ACCOUNTING'/*jai_constants.payment_voided*/ THEN --Modified by Junjian for bug#14459664 on 08-Aug-2012
949:
950: lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
951: OPEN c_repo_recovered_amt(jai_constants.source_ap,
952: pv_payment_table_name,
947: -- following elsif is added to take care of void scenarios, where in the recovered amt againt the main payment is reversed
948: ELSIF lv_source_trx_type = 'REVERSE_AVOID_ACCOUNTING'/*jai_constants.payment_voided*/ THEN --Modified by Junjian for bug#14459664 on 08-Aug-2012
949:
950: lv_codepath := jai_general_pkg.plot_codepath(18, lv_codepath);
951: OPEN c_repo_recovered_amt(jai_constants.source_ap,
952: pv_payment_table_name,
953: r_payment.reversal_inv_pmt_id,
954: r_ref.reference_id);
955: FETCH c_repo_recovered_amt
966: (nvl(r_payment.discount_taken, 0) /
967: nvl(r_payment.amount, 1));
968:
969: -- following elsif is added to take care of Prepay Unapply scenarios, where in the recovered amt againt the main payment is reversed
970: ELSIF lv_source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'/*jai_constants.prepay_unapplication*/ THEN --Modified by Junjian for bug#14459664 on 08-Aug-2012
971: lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
972: OPEN c_repo_recovered_amt(jai_constants.source_ap,
973: pv_payment_table_name,
974: r_prepayment.parent_reversal_id,
968:
969: -- following elsif is added to take care of Prepay Unapply scenarios, where in the recovered amt againt the main payment is reversed
970: ELSIF lv_source_trx_type = 'REVERSE_UNAPPLY_ACCOUNTING'/*jai_constants.prepay_unapplication*/ THEN --Modified by Junjian for bug#14459664 on 08-Aug-2012
971: lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
972: OPEN c_repo_recovered_amt(jai_constants.source_ap,
973: pv_payment_table_name,
974: r_prepayment.parent_reversal_id,
975: r_ref.reference_id);
976: FETCH c_repo_recovered_amt
1077: lv_repository_name := lv_source_trx_type;
1078: ln_entered_amt := ln_recovered_amount;
1079: ln_accounted_amt := ln_entered_amt * nvl(r_payment.exchange_rate, 1);
1080:
1081: lv_account_name := jai_constants.liability_interim;
1082: lv_codepath := jai_general_pkg.plot_codepath(20, lv_codepath);
1083:
1084: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => r_ref.reference_id,
1085: p_organization_id => ln_organization_id,
1086: p_location_id => ln_location_id,
1087: p_service_type_code => lv_service_type_code,
1088: p_process_flag => lv_process_flag,
1089: p_process_message => lv_process_message);
1090: IF lv_process_flag <> jai_constants.successful THEN
1091: lv_codepath := jai_general_pkg.plot_codepath(20.1, lv_codepath);
1092: FND_FILE.put_line(fnd_file.log,
1093: 'Error Flag:' || lv_process_flag ||
1094: ' Error Message:' || lv_process_message);
1103: lv_codepath := jai_general_pkg.plot_codepath(23, lv_codepath);
1104: jai_cmn_rgm_recording_pkg.insert_repository_entry(p_repository_id => ln_repository_id,
1105: p_regime_id => pn_regime_id,
1106: p_tax_type => lv_tax_type,
1107: p_organization_type => jai_constants.service_tax_orgn_type,
1108: p_organization_id => ln_organization_id,
1109: p_location_id => ln_location_id,
1110: p_service_type_code => lv_service_type_code,
1111: p_source => jai_constants.source_ap,
1107: p_organization_type => jai_constants.service_tax_orgn_type,
1108: p_organization_id => ln_organization_id,
1109: p_location_id => ln_location_id,
1110: p_service_type_code => lv_service_type_code,
1111: p_source => jai_constants.source_ap,
1112: p_source_trx_type => lv_source_trx_type,
1113: p_source_table_name => lv_source_table,
1114: p_source_document_id => pn_invoice_payment_id,
1115: p_transaction_date => ld_gl_date,
1135: rec_get_curr_dtls.exchange_rate_type),
1136: p_curr_conv_rate => nvl(r_payment.exchange_rate,
1137: rec_get_curr_dtls.exchange_rate),
1138: p_trx_amount => ln_entered_amt,
1139: p_accntg_required_flag => jai_constants.yes,
1140: p_accrual_basis => 'Y');
1141:
1142: IF lv_process_flag = jai_constants.expected_error OR
1143: lv_process_flag = jai_constants.unexpected_error THEN
1138: p_trx_amount => ln_entered_amt,
1139: p_accntg_required_flag => jai_constants.yes,
1140: p_accrual_basis => 'Y');
1141:
1142: IF lv_process_flag = jai_constants.expected_error OR
1143: lv_process_flag = jai_constants.unexpected_error THEN
1144: fnd_file.put_line(fnd_file.log,
1145: '5.1 ERROR IN CALL TO jai_cmn_reverse_charge_pkg.process_charge_acct - lv_process_flag ' ||
1146: lv_process_flag || ', lv_process_message' ||
1139: p_accntg_required_flag => jai_constants.yes,
1140: p_accrual_basis => 'Y');
1141:
1142: IF lv_process_flag = jai_constants.expected_error OR
1143: lv_process_flag = jai_constants.unexpected_error THEN
1144: fnd_file.put_line(fnd_file.log,
1145: '5.1 ERROR IN CALL TO jai_cmn_reverse_charge_pkg.process_charge_acct - lv_process_flag ' ||
1146: lv_process_flag || ', lv_process_message' ||
1147: lv_process_message);
1156:
1157: jai_cmn_rgm_recording_pkg.exc_gain_loss_accounting(p_repository_id => ln_rgm_reposotory_id,
1158: p_regime_id => pn_regime_id,
1159: p_tax_type => r_ref.tax_type,
1160: p_organization_type => jai_constants.orgn_type_io,
1161: p_organization_id => ln_organization_id,
1162: p_location_id => ln_location_id,
1163: p_source => jai_constants.source_ap,
1164: p_source_trx_type => lv_source_trx_type,
1159: p_tax_type => r_ref.tax_type,
1160: p_organization_type => jai_constants.orgn_type_io,
1161: p_organization_id => ln_organization_id,
1162: p_location_id => ln_location_id,
1163: p_source => jai_constants.source_ap,
1164: p_source_trx_type => lv_source_trx_type,
1165: p_source_table_name => lv_source_table,
1166: p_source_document_id => pn_invoice_payment_id,
1167: p_transaction_date => ld_transaction_date,
1174: p_process_flag => lv_process_flag,
1175: p_process_message => lv_process_message,
1176: p_accounting_date => ld_accounting_date);
1177:
1178: IF lv_process_flag = jai_constants.expected_error OR
1179: lv_process_flag = jai_constants.unexpected_error THEN
1180: p_process_flag := lv_process_flag;
1181: p_process_message := lv_process_message;
1182: fnd_file.put_line(fnd_file.log,
1175: p_process_message => lv_process_message,
1176: p_accounting_date => ld_accounting_date);
1177:
1178: IF lv_process_flag = jai_constants.expected_error OR
1179: lv_process_flag = jai_constants.unexpected_error THEN
1180: p_process_flag := lv_process_flag;
1181: p_process_message := lv_process_message;
1182: fnd_file.put_line(fnd_file.log,
1183: 'error in call to jai_cmn_reverse_charge_pkg.process_charge_acct - lv_process_flag ' ||
1186: RETURN;
1187: END IF;
1188: END IF;
1189:
1190: jai_cmn_rgm_recording_pkg.update_reference(p_source => jai_constants.source_ap,
1191: p_reference_id => r_ref.reference_id,
1192: p_recovered_amount => ln_recovered_amount,
1193: p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
1194: p_process_flag => p_process_flag,
1193: p_discounted_amount => ln_discounted_amount, -- CHK (Implementation)
1194: p_process_flag => p_process_flag,
1195: p_process_message => p_process_message);
1196:
1197: p_process_flag := jai_constants.successful;
1198: IF g_debug = 'Y' THEN
1199: FND_FILE.put_line(fnd_file.log, 'Codepath:' || lv_codepath);
1200: END IF;
1201:
1332: from JAI_RGM_ORG_REGNS_V
1333: where regime_id =
1334: (SELECT regime_id
1335: FROM JAI_RGM_DEFINITIONS
1336: WHERE regime_code = jai_constants.service_regime)
1337: and organization_id = p_organization_id
1338: and location_id = p_location_id
1339: AND attribute_code = 'EFF_DATE_ST_PT'
1340: AND attribute_type_code = 'OTHERS'
1502: jai_rgm_trx_refs jrf,
1503: JAI_CMN_TAXES_ALL jct
1504: Where jrr.reference_id = jrf.reference_id
1505: AND jrr.settlement_id = pn_settlement_id
1506: AND jrf.source = jai_constants.source_ap
1507: AND jrr.source = jai_constants.source_ap
1508: AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
1509: AND jrf.tax_id = jct.tax_id
1510: AND jct.reverse_charge_flag = 'Y'
1503: JAI_CMN_TAXES_ALL jct
1504: Where jrr.reference_id = jrf.reference_id
1505: AND jrr.settlement_id = pn_settlement_id
1506: AND jrf.source = jai_constants.source_ap
1507: AND jrr.source = jai_constants.source_ap
1508: AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
1509: AND jrf.tax_id = jct.tax_id
1510: AND jct.reverse_charge_flag = 'Y'
1511: GROUP BY jrf.invoice_id,
1536: OPEN cur_regime_code;
1537: FETCH cur_regime_code INTO lv_regime_code;
1538: CLOSE cur_regime_code;
1539:
1540: IF lv_regime_code = jai_constants.service_regime THEN
1541: lv_organization_type := jai_constants.orgn_type_io;
1542: ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1543: END IF;
1544: /*Insert recovery accounting entry into GL INTERFACE*/
1537: FETCH cur_regime_code INTO lv_regime_code;
1538: CLOSE cur_regime_code;
1539:
1540: IF lv_regime_code = jai_constants.service_regime THEN
1541: lv_organization_type := jai_constants.orgn_type_io;
1542: ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1543: END IF;
1544: /*Insert recovery accounting entry into GL INTERFACE*/
1545: ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
1538: CLOSE cur_regime_code;
1539:
1540: IF lv_regime_code = jai_constants.service_regime THEN
1541: lv_organization_type := jai_constants.orgn_type_io;
1542: ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1543: END IF;
1544: /*Insert recovery accounting entry into GL INTERFACE*/
1545: ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
1546: (
1548: p_organization_type => lv_organization_type,
1549: p_organization_id => r_rec.organization_id,
1550: p_location_id => r_rec.location_id,
1551: p_tax_type => r_rec.tax_type,
1552: p_account_name => jai_constants.recovery
1553: );
1554:
1555:
1556: jai_cmn_reverse_charge_pkg.post_accounting(
1557: p_regime_code => lv_regime_code,
1558: p_tax_type => r_rec.tax_type,
1559: p_organization_type => lv_organization_type,
1560: p_organization_id => r_rec.organization_id,
1561: p_source => jai_constants.source_ap,
1562: p_source_trx_type => 'INVOICE_SETTLEMENT',
1563: p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1564: p_source_document_id => pn_settlement_id,
1565: p_code_combination_id => ln_charge_accounting_id,
1569: p_accounted_dr => ln_recovery_amount,
1570: p_accounting_date => trunc(sysdate),
1571: p_transaction_date => trunc(sysdate),
1572: p_calling_object => 'JAIRGMSP',
1573: p_repository_name => jai_constants.repository_name,
1574: p_invoice_id => r_rec.invoice_id,
1575: p_reference_name => NULL,
1576: p_reference_id => NULL,
1577: p_currency_code => jai_constants.func_curr);
1573: p_repository_name => jai_constants.repository_name,
1574: p_invoice_id => r_rec.invoice_id,
1575: p_reference_name => NULL,
1576: p_reference_id => NULL,
1577: p_currency_code => jai_constants.func_curr);
1578:
1579: /*Insert reverse charge accounting entry into GL INTERFACE*/
1580: ln_charge_accounting_id :=jai_cmn_rgm_recording_pkg.get_account
1581: (
1591: p_regime_code => lv_regime_code,
1592: p_tax_type => r_rec.tax_type,
1593: p_organization_type => lv_organization_type,
1594: p_organization_id => r_rec.organization_id,
1595: p_source => jai_constants.source_ap,
1596: p_source_trx_type => 'INVOICE_SETTLEMENT',
1597: p_source_table_name => 'JAI_RGM_SETTLEMENTS',
1598: p_source_document_id => pn_settlement_id,
1599: p_code_combination_id => ln_charge_accounting_id,
1603: p_accounted_dr => NULL,
1604: p_accounting_date => trunc(sysdate),
1605: p_transaction_date => trunc(sysdate),
1606: p_calling_object => 'JAIRGMSP',
1607: p_repository_name => jai_constants.repository_name,
1608: p_invoice_id => r_rec.invoice_id,
1609: p_reference_name => NULL,
1610: p_reference_id => NULL,
1611: p_currency_code => jai_constants.func_curr);
1607: p_repository_name => jai_constants.repository_name,
1608: p_invoice_id => r_rec.invoice_id,
1609: p_reference_name => NULL,
1610: p_reference_id => NULL,
1611: p_currency_code => jai_constants.func_curr);
1612: end if;
1613: END loop;
1614: EXCEPTION
1615: When others then
1612: end if;
1613: END loop;
1614: EXCEPTION
1615: When others then
1616: pv_process_flag := jai_constants.unexpected_error;
1617: pv_process_message := 'SQLCODE='||TO_CHAR(SQLCODE)||' settlement_payment Occured:'||SQLERRM;
1618: END process_settlement_payment_acc;
1619:
1620:
1728:
1729: lv_reference1 := null ;
1730:
1731:
1732: IF p_regime_code = jai_constants.service_regime
1733: AND p_source_trx_type= 'INVOICE_SETTLEMENT' THEN
1734:
1735: OPEN c_organization_code(p_organization_id);
1736: FETCH c_organization_code INTO lv_organization_code;
1745: lv_invoice_num_str := 'Invoice Number: ' || lv_invoice_num;
1746:
1747: lv_reference10 := 'Service Tax Accounting for '||p_source||'. Transaction Type:'||nvl(p_source_trx_type,'~~')
1748: || '. ' ||lv_invoice_num_str;
1749: lv_source_name := jai_constants.service_tax_source;
1750:
1751:
1752: ld_accounting_date := nvl( trunc(p_accounting_date), trunc(sysdate) );
1753:
1777: p_accounting_date => ld_accounting_date,
1778: p_period_name => lv_period_name,
1779: p_sob_id => ln_sob_id
1780: );
1781: lv_gl_je_category := jai_constants.je_category_rg_entry;
1782: lv_status := 'NEW';
1783:
1784: INSERT INTO gl_interface (
1785: status, set_of_books_id, user_je_source_name, user_je_category_name,
1792: lv_status, ln_sob_id, lv_source_name, lv_gl_je_category,
1793: ld_accounting_date, p_currency_code, sysdate, FND_GLOBAL.user_id,
1794: 'A', ln_entered_cr, ln_entered_dr, ln_accounted_cr, ln_accounted_dr, p_transaction_date,
1795: p_code_combination_id, p_curr_conv_date, p_curr_conv_type, p_curr_conv_rate,
1796: lv_reference10, jai_constants.gl_je_source_name, p_calling_object, lv_reference1,
1797: p_source_table_name, p_source_document_id, p_invoice_id ,p_organization_id
1798: );
1799:
1800: IF p_reference_id IS NOT NULL OR p_reference_name IS NOT NULL THEN
1843: SELECT jrs.*
1844: FROM JAI_RGM_SETTLEMENTS jrs,
1845: JAI_RGM_DEFINITIONS jrd
1846: where jrs.regime_id = jrd.regime_id
1847: and jrd.regime_code = jai_constants.service_regime
1848: and jrs.payment_date between p_from_date and p_to_date
1849: and jrs.status = 'SETTLED';
1850:
1851:
1866: jai_rgm_trx_refs jrf,
1867: JAI_CMN_TAXES_ALL jct
1868: Where jrr.reference_id = jrf.reference_id
1869: AND jrr.settlement_id = pn_settlement_id
1870: AND jrf.source = jai_constants.source_ap
1871: AND jrr.organization_id = nvl(p_organization_id,jrr.organization_id)
1872: AND jrr.source = jai_constants.source_ap
1873: AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
1874: AND jrf.tax_id = jct.tax_id
1868: Where jrr.reference_id = jrf.reference_id
1869: AND jrr.settlement_id = pn_settlement_id
1870: AND jrf.source = jai_constants.source_ap
1871: AND jrr.organization_id = nvl(p_organization_id,jrr.organization_id)
1872: AND jrr.source = jai_constants.source_ap
1873: AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' )
1874: AND jrf.tax_id = jct.tax_id
1875: AND jct.reverse_charge_flag = 'Y'
1876: GROUP BY jrf.reference_id,
1902: FROM jai_rgm_trx_records jrr,
1903: jai_rgm_trx_refs jrf
1904: Where jrr.reference_id = jrf.reference_id
1905: AND jrf.invoice_id = pn_invoice_id
1906: AND jrr.source = jai_constants.source_ap
1907: AND jrr.source_trx_type = 'INVOICE_SETTLEMENT'
1908: AND jrr.source_table_name = 'JAI_RGM_SETTLEMENTS'
1909: AND jrr.source_document_id = pn_settlement_id
1910: AND jrf.reference_id = pn_reference_id
1916: FROM jai_rgm_trx_records jrr,
1917: jai_rgm_trx_refs jrf
1918: Where jrr.reference_id = jrf.reference_id
1919: AND jrr.settlement_id = pn_settlement_id
1920: AND jrf.source = jai_constants.source_ap
1921: AND jrr.source = jai_constants.source_ap
1922: AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' );
1923:
1924: CURSOR c_get_reference_id ( pn_invoice_id NUMBER, pn_invoice_line_id NUMBER, pn_tax_id NUMBER )IS
1917: jai_rgm_trx_refs jrf
1918: Where jrr.reference_id = jrf.reference_id
1919: AND jrr.settlement_id = pn_settlement_id
1920: AND jrf.source = jai_constants.source_ap
1921: AND jrr.source = jai_constants.source_ap
1922: AND jrr.source_trx_type in ( 'REVERSE_PAID_ACCOUNTING', 'REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING' );
1923:
1924: CURSOR c_get_reference_id ( pn_invoice_id NUMBER, pn_invoice_line_id NUMBER, pn_tax_id NUMBER )IS
1925: SELECT jrf.reference_id
1940: and al.invoice_id = pn_invoice_id
1941: and ad.invoice_distribution_id = pn_invoice_line_id
1942: and aila.reference_key1 = pn_tax_id
1943: )
1944: AND jrf.source = jai_constants.source_ap
1945: AND jrf.invoice_id = pn_invoice_id
1946: AND jrf.line_id = aida.invoice_distribution_id
1947: AND jrf.tax_id = pn_tax_id;
1948:
1986: OPEN c_get_service_type_code(r_sel.settlement_id,r_rec.invoice_id);
1987: FETCH c_get_service_type_code INTO lv_service_type_code;
1988: CLOSE c_get_service_type_code;
1989:
1990: IF lv_regime_code = jai_constants.service_regime THEN
1991: lv_organization_type := jai_constants.orgn_type_io;
1992: ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1993: END IF;
1994:
1987: FETCH c_get_service_type_code INTO lv_service_type_code;
1988: CLOSE c_get_service_type_code;
1989:
1990: IF lv_regime_code = jai_constants.service_regime THEN
1991: lv_organization_type := jai_constants.orgn_type_io;
1992: ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1993: END IF;
1994:
1995: jai_cmn_rgm_recording_pkg.insert_repository_entry(
1988: CLOSE c_get_service_type_code;
1989:
1990: IF lv_regime_code = jai_constants.service_regime THEN
1991: lv_organization_type := jai_constants.orgn_type_io;
1992: ln_recovery_amount := ROUND(ln_recovery_amount, jai_constants.service_rgm_rnd_factor);
1993: END IF;
1994:
1995: jai_cmn_rgm_recording_pkg.insert_repository_entry(
1996: p_repository_id => ln_repository_id,
1999: p_organization_type => lv_organization_type,
2000: p_organization_id => r_rec.organization_id,
2001: p_location_id => r_rec.location_id,
2002: p_service_type_code => lv_service_type_code,
2003: p_source => jai_constants.source_ap,
2004: p_source_trx_type => 'INVOICE_SETTLEMENT',
2005: p_source_table_name => 'JAI_RGM_SETTLEMENTS',
2006: p_source_document_id => r_sel.settlement_id,
2007: p_transaction_date => r_sel.PAYMENT_DATE,
2023: p_curr_conv_date => r_curr_dtls.exchange_date,
2024: p_curr_conv_type => r_curr_dtls.exchange_rate_type,
2025: p_curr_conv_rate => r_curr_dtls.exchange_rate,
2026: p_trx_amount => ln_recovery_amount,
2027: p_accntg_required_flag => jai_constants.no,
2028: p_accrual_basis => 'N');
2029:
2030: IF lv_process_flag = jai_constants.expected_error OR
2031: lv_process_flag = jai_constants.unexpected_error THEN
2026: p_trx_amount => ln_recovery_amount,
2027: p_accntg_required_flag => jai_constants.no,
2028: p_accrual_basis => 'N');
2029:
2030: IF lv_process_flag = jai_constants.expected_error OR
2031: lv_process_flag = jai_constants.unexpected_error THEN
2032: fnd_file.put_line( fnd_file.log,
2033: '5.1 ERROR IN CALL TO jai_cmn_reverse_charge_pkg.process_settlement_repository - lv_process_flag '||lv_process_flag
2034: ||', lv_process_message'||lv_process_message);
2027: p_accntg_required_flag => jai_constants.no,
2028: p_accrual_basis => 'N');
2029:
2030: IF lv_process_flag = jai_constants.expected_error OR
2031: lv_process_flag = jai_constants.unexpected_error THEN
2032: fnd_file.put_line( fnd_file.log,
2033: '5.1 ERROR IN CALL TO jai_cmn_reverse_charge_pkg.process_settlement_repository - lv_process_flag '||lv_process_flag
2034: ||', lv_process_message'||lv_process_message);
2035: p_process_flag := lv_process_flag;
2040: END LOOP;
2041:
2042: EXCEPTION
2043: WHEN OTHERS THEN
2044: p_process_flag := jai_constants.unexpected_error;
2045: p_process_message := 'Repository Error SQLCODE='||TO_CHAR(SQLCODE)||' SQLERRM=:'||SQLERRM;
2046: Fnd_file.put_line( fnd_file.log, p_process_message);
2047: END process_settlement_repository;
2048: