[Home] [Help]
5: Created By : aiyer
6: Created Date : 27-jan-2005
7: Enhancement Bug : 4146634
8: Purpose : Process the Service Tax AR records (Invoices,Credit memo's and Cash Receipts Applications) and populate
9: the jai_rgm_trx_refs and jai_rgms_trx_records appropriately.
10: Called From : jai_rgm_trx_processing.process_batch
11:
12: Dependency Due To The Current Bug :
13: This object has been newly created with as a part of the service tax enhancement.
49: Issue : Invoice date column in 'India - Service tax pending liability' report
50: is showing creation date. (The reason for making changes in this file is that
51: the service tax processing program's behavior was also wrong w.r.to the date
52: paratmeters passed and the way AR invoices are picked up for populating
53: jai_rgm_trx_refs table
54: Cause : The query in the report is using jai_rgm_trx_refs.creation_date as
55: the invoice date. While checking how this field is populated, observed that
56: the records are populated while running the service tax processor.
57: In the procedure populate_inv_cm_references, the invoices are picked up
50: is showing creation date. (The reason for making changes in this file is that
51: the service tax processing program's behavior was also wrong w.r.to the date
52: paratmeters passed and the way AR invoices are picked up for populating
53: jai_rgm_trx_refs table
54: Cause : The query in the report is using jai_rgm_trx_refs.creation_date as
55: the invoice date. While checking how this field is populated, observed that
56: the records are populated while running the service tax processor.
57: In the procedure populate_inv_cm_references, the invoices are picked up
58: based on the creation_date. This is wrong behavior, and by common sense it
171: Porting the changes done in 12.1 branch.
172: Issue:
173: Service tax pending liability report is showing the post POT invoices which are completed
174: technical details:
175: issue is due to the jai_rgm_trx_refs.recovered_amount is not updated with the tax amount
176: Fix:
177: made a call to jai_rgm_trx_recording_pkg.update_reference to update the recovered_amoun in
178: populate_repository procedure
179:
205: 26. 21-jul-2011 vkaranam for bug#12777500
206: Issue:
207: Service tax pending liability report is showing the post POT invoices which are completed
208: technical details:
209: issue is due to the jai_rgm_trx_refs.recovered_amount is not updated with the tax amount
210: Fix:
211: made a call to jai_rgm_trx_recording_pkg.update_reference to update the recovered_amoun in
212: populate_repository procedure
213:
220: Fixed: for Tax line from Open Interface and External and GL date >= POT date should not populate repository
221: 30. 21-Nov-2011 Qiong for Advanced Receipts
222:
223: 31. 30-Nov-2011 Qinglei for Bug#13531399
224: Fixed: for both recoverable and non-recovable tax, when poputlate to jai_rgm_trx_refs, use tax amount
225: ignore the mod_cr_percentage.
226:
227: 32. 05-Jan-2012 Qinglei for Bug#13556198
228: Fixed: Receipt application/unapplication can't updated into Service Tax Repository
539: END get_regime_info;
540:
541: procedure get_ar_tax_disc_accnt ( p_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE ,
542: p_org_id AR_RECEIVABLES_TRX_ALL.ORG_ID%TYPE ,
543: p_total_disc_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ,/*Parameter added for the bug 4193633 */
544: p_tax_ediscounted OUT NOCOPY AR_RECEIVABLE_APPLICATIONS_ALL.TAX_EDISCOUNTED%TYPE ,
545: p_earned_disc_ccid OUT NOCOPY AR_RECEIVABLES_TRX_ALL.CODE_COMBINATION_ID%TYPE ,
546: p_tax_uediscounted OUT NOCOPY AR_RECEIVABLE_APPLICATIONS_ALL.TAX_UEDISCOUNTED%TYPE ,
547: p_unearned_disc_ccid OUT NOCOPY AR_RECEIVABLES_TRX_ALL.CODE_COMBINATION_ID%TYPE ,
553: Created By : aiyer
554: Created Date : 27-jan-2005
555: Enhancement Bug : 4146634
556: Purpose : Gte the tax earned and Unearned discounts associated with the Receivable application
557: the jai_rgm_trx_refs and jai_rgms_trx_records appropriately.
558: Called From : jai_cmn_rgm_recording_pkg.insert_reference
559:
560: Change History: -
561: =================
622: status = p_status; --rchandan for bug#4428980
623:
624: rec_cur_get_receivable_app CUR_GET_RECEIVABLE_APP%ROWTYPE ;
625: rec_cur_get_disc_ccid CUR_GET_DISC_CCID%ROWTYPE ;
626: ln_total_rec_disc_amt JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
627:
628: BEGIN
629:
630: fnd_file.put_line(fnd_file.LOG,'********************* 1 START OF PROCEDURE jai_ar_rgm_processing_pkg.GET_AR_TAX_DISC_ACCNT *********************');
749: p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
750: p_from_date IN DATE ,
751: p_to_date IN DATE ,
752: p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
753: p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
754: p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
755: p_process_flag OUT NOCOPY VARCHAR2 ,
756: p_process_message OUT NOCOPY VARCHAR2,
757: p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL
759: IS
760: /******************************
761: ||Variable Declaration Section
762: *******************************/
763: ln_reference_id JAI_RGM_TRX_REFS.REFERENCE_ID%TYPE ;
764: lv_process_flag VARCHAR2(2) ;
765: lv_process_message VARCHAR2(1996) ;
766: ln_invoices_processed NUMBER(10) ;
767: ln_receipts_processed NUMBER(10) ;
868: )
869: ) AND
870: NOT EXISTS ( SELECT /*A ref of invoice/cm should not exist in the reference table */
871: 1
872: FROM jai_rgm_trx_refs rgtr
873: WHERE
874: rgtr.source = p_source AND
875: rgtr.invoice_id = trx.customer_trx_id AND
876: rgtr.line_id = jtrxtl.customer_trx_line_id AND
935: AND jcra.organization_id = p_organization_id
936: AND jcra.org_id = nvl(p_org_id,jcra.org_id)
937: AND NOT EXISTS ( SELECT /*A ref of receipt should not exist in the reference table */
938: 1
939: FROM jai_rgm_trx_refs rgtr
940: WHERE
941: rgtr.source = p_source AND
942: rgtr.invoice_id = acra.cash_receipt_id AND
943: rgtr.line_id = jdt.doc_tax_id AND
999: --Added by Qiong for reverse charge bug#16001407 end
1000:
1001:
1002: /****
1003: ||insert the invoices and credit memo's into the jai_rgm_trx_refs
1004: ||using the procedure jai_cmn_rgm_recording_pkg.insert_reference
1005: *****/
1006: fnd_file.put_line(fnd_file.LOG,'3 before call to procedure jai_cmn_rgm_recording_pkg.insert_reference ');
1007:
1084: p_process_message := lv_process_message ;
1085: END IF;
1086:
1087: ln_invoices_processed := ln_invoices_processed + 1;
1088: fnd_file.put_line(fnd_file.LOG,'6 inserted record in jai_rgm_trx_refs with reference_id '||ln_reference_id );
1089:
1090: END IF; -- added by zhiwei.xin for bug 13535100 on 07-Feb-2012
1091: end if;--Added by qiong.liu for bug 16001407
1092:
1107: ||', recoverable_amount '||rec_c_fetch_receipt_rec.tax_amount * (rec_c_fetch_receipt_rec.mod_cr_percentage/100)
1108: );
1109:
1110: /****
1111: ||insert the receipts into the jai_rgm_trx_refs
1112: ||using the procedure jai_cmn_rgm_recording_pkg.insert_reference
1113: *****/
1114: fnd_file.put_line(fnd_file.LOG,'2.2 before call to procedure jai_cmn_rgm_recording_pkg.insert_reference ');
1115: savepoint before_ref_receipt;
1167: p_process_message := lv_process_message ;
1168: END IF;
1169:
1170: ln_receipts_processed := ln_receipts_processed + 1;
1171: fnd_file.put_line(fnd_file.LOG,'2.5 inserted record in jai_rgm_trx_refs with reference_id '||ln_reference_id );
1172: END LOOP;
1173: --Added by Wenqiong End
1174: EXCEPTION
1175: WHEN OTHERS THEN
1185: p_from_date IN DATE ,
1186: p_to_date IN DATE ,
1187: p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
1188: p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1189: p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
1190: p_process_flag OUT NOCOPY VARCHAR2 ,
1191: p_process_message OUT NOCOPY VARCHAR2
1192: ,p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL)
1193: IS
1194:
1195: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1196: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1197: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1198: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1199: ln_err_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1200: ln_set_save_point JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1201: lv_process_flag VARCHAR2(2) ;
1202: lv_process_message VARCHAR2(1996) ;
1195: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1196: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1197: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1198: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1199: ln_err_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1200: ln_set_save_point JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1201: lv_process_flag VARCHAR2(2) ;
1202: lv_process_message VARCHAR2(1996) ;
1203: lv_source_trx_type VARCHAR2(50) ;
1196: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1197: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1198: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1199: ln_err_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1200: ln_set_save_point JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1201: lv_process_flag VARCHAR2(2) ;
1202: lv_process_message VARCHAR2(1996) ;
1203: lv_source_trx_type VARCHAR2(50) ;
1204: ln_uncommitted_transactions NUMBER(10) := 0 ;
1208: /* Added by Ramananda for bug#4407165 */
1209: lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_rgm_processing_pkg.delete_non_existant_cm';
1210:
1211: /*
1212: || Get all the cm's which exist in the reference table jai_rgm_trx_refs and have been incompleted/incompleted and deleted from AR base table
1213: || IF a CM has been incompleted then it would exist with complete_flag = 'N' . if a CM has been incompleted and deleted then it would not exist
1214: || in the ra_customer_trx_all table.
1215: */
1216: CURSOR c_get_incompleted_cm
1219: rgtr.invoice_id cm_customer_trx_id /*,
1220: rgtr.reference_id cm_reference_id */
1221: , rgtr.line_id --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
1222: FROM
1223: jai_rgm_trx_refs rgtr
1224: WHERE
1225: rgtr.source = p_source AND
1226: rgtr.organization_id = p_organization_id AND /*5879769*/
1227: rgtr.reversal_flag = 'Y' AND
1245: || Get all the data of the incompleted CM from the
1246: || repository, so that the same record with an exactly opposite amount can be passed. This would be the CM-CM-REV record
1247: */
1248: CURSOR c_get_cm_cm_app_rec ( /*cp_cm_reference_id JAI_RGM_TRX_RECORDS.REFERENCE_ID%TYPE ,*/
1249: cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE,
1250: cp_attribute_context jai_rgm_trx_records.trx_reference_context%TYPE --rchandan for bug#4428980 --Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
1251: )
1252: IS
1253: SELECT
1261:
1262: /*
1263: || Update all the credit memo reference records.
1264: */
1265: CURSOR cur_upd_cm_ref ( cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE )
1266: IS
1267: SELECT
1268: *
1269: FROM
1266: IS
1267: SELECT
1268: *
1269: FROM
1270: jai_rgm_trx_refs
1271: WHERE
1272: invoice_id = cp_cm_customer_trx_id AND
1273: nvl(recovered_amount,0) <> 0 ;
1274:
1275: /*
1276: || Get all the data of the CM applied to invoices (i.e CM-INV-APP) from the repository.
1277: || So that the same record with an exactly opposite amount can be passed. This would be the new CM-INV-REV record
1278: */
1279: CURSOR c_get_cm_inv_app_rec ( cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE
1280: -- cp_attribute_context jai_rgm_trx_records.attribute_context%TYPE ) --Commented by Xiao for bug#6773751
1281: )
1282: IS
1283: SELECT
1684: p_from_date IN DATE ,
1685: p_to_date IN DATE ,
1686: p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
1687: p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1688: p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
1689: p_process_flag OUT NOCOPY VARCHAR2 ,
1690: p_process_message OUT NOCOPY VARCHAR2
1691: , p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL )
1692: IS
1691: , p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL )
1692: IS
1693:
1694: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1695: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1696: ln_cm_ref_upd JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE ;
1697: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1698: lv_source_trx_type VARCHAR2(50) ;
1699: ln_eff_cm_tottax_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1692: IS
1693:
1694: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1695: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1696: ln_cm_ref_upd JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE ;
1697: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1698: lv_source_trx_type VARCHAR2(50) ;
1699: ln_eff_cm_tottax_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1700: ln_tot_effcm_rb_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1695: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1696: ln_cm_ref_upd JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE ;
1697: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1698: lv_source_trx_type VARCHAR2(50) ;
1699: ln_eff_cm_tottax_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1700: ln_tot_effcm_rb_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1701: ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
1702: ln_sign_of_credit_memo NUMBER ;
1703: ln_cm_ref_ratio NUMBER ;
1696: ln_cm_ref_upd JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE ;
1697: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1698: lv_source_trx_type VARCHAR2(50) ;
1699: ln_eff_cm_tottax_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1700: ln_tot_effcm_rb_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1701: ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
1702: ln_sign_of_credit_memo NUMBER ;
1703: ln_cm_ref_ratio NUMBER ;
1704: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1727:
1728: /*
1729: || Get the credit memo applications to invoices and the total invoice tax amount .
1730: || Consider only those credit memo applications which follow the following conditions:-
1731: || 1. Invoice tax line reference exists in the jai_rgm_trx_refs table for the invoice against which the cash receipt is being applied
1732: || 2. The cash receipt tax line does not already exist in the repository i.e jai_rgm_trx_records.
1733: || 3. Consider cash receipt application against an invoice only
1734: || 4. Invoice tax line has not been fully recovered i.e recovered_amount < recoverable_amount - discounted_amount in references table
1735: || 5. Processes DebitMemo for bug#6773751, add by Xiao on 20-Dec-09.
1797: EXISTS (
1798: SELECT /* A credit memo exists in the reference table with total recoverable amount <> recovered amount*/
1799: 1
1800: FROM
1801: jai_rgm_trx_refs rgtf
1802: WHERE
1803: rgtf.source = cp_source_ar AND
1804: rgtf.invoice_id = aral.customer_trx_id AND
1805: nvl(rgtf.recoverable_amount,0) <> nvl(rgtf.recovered_amount,0)
1807: EXISTS (
1808: SELECT /* A invoice exists in the reference table with total recoverable amount > recovered amount*/
1809: 1
1810: FROM
1811: jai_rgm_trx_refs rgtf
1812: WHERE
1813: rgtf.source = cp_source_ar AND
1814: rgtf.invoice_id = aral.applied_customer_trx_id AND
1815: nvl(rgtf.recoverable_amount,0) - nvl(discounted_amount,0) > nvl(rgtf.recovered_amount,0)
1893: item_line_id /*5879769*/
1894: , line_id --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
1895:
1896: FROM
1897: jai_rgm_trx_refs
1898: WHERE
1899: source = cp_source_ar AND
1900: invoice_id = cp_inv_customer_trx_id AND
1901: nvl(recoverable_amount,0) - nvl(discounted_amount,0) > nvl(recovered_amount,0)
1914: nvl(recovered_amount,0) recovered_amount ,
1915: item_line_id /*5879769*/
1916: , line_id --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
1917: FROM
1918: jai_rgm_trx_refs
1919: WHERE
1920: source = cp_source_ar AND
1921: invoice_id = cp_cm_customer_trx_id AND
1922: nvl(recoverable_amount,0) <> nvl(recovered_amount,0)
1931: IS
1932: SELECT
1933: nvl(sum(recoverable_amount),0) tot_effcm_rb_amt /*bug 9432780*/
1934: FROM
1935: jai_rgm_trx_refs
1936: WHERE
1937: source = cp_source_ar AND
1938: invoice_id = cp_cm_customer_trx_id ;
1939:
2554: p_from_date IN DATE ,
2555: p_to_date IN DATE ,
2556: p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
2557: p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
2558: p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
2559: p_process_flag OUT NOCOPY VARCHAR2 ,
2560: p_process_message OUT NOCOPY VARCHAR2
2561: ,p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL )
2562: IS
2588: ln_trx_amount NUMBER; -- Added for Bug 10623735
2589: /*
2590: || Get the cash receipt, Invoice and Total Effective Invoice Recoverable Amount for them.
2591: || Consider only those cash receipts which follow the conditions as given below:-
2592: || 1. Invoice tax line reference exists in the jai_rgm_trx_refs table for the invoice against which the cash receipt is being applied
2593: || 2. The cash receipt tax line does not already exist in the repository i.e jai_rgm_trx_records.
2594: || 3. Consider cash receipt application against an invoice only
2595: || 4. Invoice tax line has not been fully recovered i.e recovered_amount < recoverable_amount in references table
2596: || 5.Consider Cash Receipt application for DM ,bug #6773751
2661: EXISTS (
2662: SELECT /* A invoice exists in the reference table with total recoverable amount - discounted_amount > recovered amount*/
2663: 1
2664: FROM
2665: jai_rgm_trx_refs rgtf
2666: WHERE
2667: rgtf.source = jai_constants.SOURCE_AR AND
2668: rgtf.invoice_id = aral.applied_customer_trx_id AND
2669: (
2760: nvl(recovered_amount,0) recovered_amount ,
2761: item_line_id /*5879769*/ ,
2762: NVL(reversal_flag,'N') reversal_flag --Added by Eric Ma for bug 10230041 on Oct-28-20100
2763: FROM
2764: jai_rgm_trx_refs
2765: WHERE
2766: source = cp_source_ar AND
2767: invoice_id = cp_customer_trx_id AND
2768:
3385: p_from_date IN DATE ,
3386: p_to_date IN DATE ,
3387: p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
3388: p_source IN varchar2 ,
3389: p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
3390: p_process_flag OUT NOCOPY VARCHAR2 ,
3391: p_process_message OUT NOCOPY VARCHAR2 ,
3392: p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL)
3393: IS
3425: rgtr.source_table_name = lv_source_table AND
3426: rgtr.source_document_id = jtrx.customer_trx_id
3427: ) AND
3428: EXISTS ( SELECT 1
3429: FROM jai_rgm_trx_refs rgtf
3430: WHERE
3431: rgtf.source = cp_source_ar AND
3432: rgtf.invoice_id = jtrx.customer_trx_id AND
3433: rgtf.party_type = 'C'
3488: AND rgtr.organization_id = p_organization_id
3489: AND rgtr.source_table_name = lv_source_table
3490: AND rgtr.source_document_id = jattl.customer_trx_line_id)
3491: AND EXISTS (SELECT 1
3492: FROM jai_rgm_trx_refs rgtf
3493: WHERE rgtf.source = cp_source_ar
3494: AND rgtf.line_id = jattl.customer_trx_line_id
3495: AND rgtf.party_type = 'C');
3496:
3500:
3501: CURSOR c_ar_ref_records(cp_source_document_id NUMBER, cp_source_ar VARCHAR2)
3502: IS
3503: SELECT *
3504: FROM jai_rgm_trx_refs rgtf
3505: WHERE line_id = cp_source_document_id AND
3506: party_type = 'C' AND
3507: source = cp_source_ar;
3508: --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
3532: AND SOURCE = 'AR';
3533:
3534: CURSOR get_total_rec_amt_cur(pn_invoice_id IN NUMBER) IS
3535: SELECT SUM(tax_amount)
3536: FROM jai_rgm_trx_refs jrtr
3537: WHERE jrtr.invoice_id = pn_invoice_id
3538: AND jrtr.source = 'AR'
3539: AND jrtr.tax_type IN (jai_constants.tax_type_service,
3540: jai_constants.tax_type_service_edu_cess,
3625: AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
3626: AND rgtr.source_trx_type = jai_constants.trx_type_adv_rcpts
3627: AND rgtr.source_document_id = jcra.cash_receipt_id)
3628: AND EXISTS (SELECT 1
3629: FROM jai_rgm_trx_refs rgtf
3630: WHERE rgtf.source = 'AR'
3631: AND rgtf.invoice_id = jcra.cash_receipt_id
3632: AND rgtf.party_type = 'C');
3633:
3649:
3650: CURSOR c_rcpt_ref_records(cp_source_document_id NUMBER, cp_source_doc_line_id NUMBER, cp_source_ar VARCHAR2)
3651: IS
3652: SELECT *
3653: FROM jai_rgm_trx_refs rgtf
3654: WHERE line_id = cp_source_doc_line_id AND
3655: invoice_id = cp_source_document_id AND
3656: party_type = 'C' AND
3657: source = cp_source_ar;
3694: --Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
3695: --by Qinglei on 24-Dec-2012 for bug#13741544
3696: AND rgtr.source_document_id = jcra.cash_receipt_id)
3697: AND EXISTS (SELECT 1
3698: FROM jai_rgm_trx_refs rgtf
3699: WHERE rgtf.source = 'AR'
3700: AND rgtf.invoice_id = jcra.cash_receipt_id
3701: AND rgtf.party_type = 'C');
3702:
3756: AND rgtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
3757: AND rgtr.source_trx_type = jai_constants.trx_type_adv_app
3758: AND rgtr.source_document_id = araa.receivable_application_id)
3759: AND EXISTS (SELECT 1
3760: FROM jai_rgm_trx_refs rgtf
3761: WHERE rgtf.source = 'AR'
3762: AND rgtf.invoice_id = jcra.cash_receipt_id
3763: AND rgtf.party_type = 'C');
3764:
3818: AND rgtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
3819: AND rgtr.source_trx_type = jai_constants.trx_type_adv_unapp
3820: AND rgtr.source_document_id = araa.receivable_application_id )
3821: AND EXISTS (SELECT 1
3822: FROM jai_rgm_trx_refs rgtf
3823: WHERE rgtf.source = 'AR'
3824: AND rgtf.invoice_id = jcra.cash_receipt_id
3825: AND rgtf.party_type = 'C');
3826: ln_receipt_amount NUMBER;
3838: ld_accounting_date DATE;
3839: ln_claim_amount NUMBER;
3840: ln_total_amount NUMBER;
3841:
3842: lr_ar_ref_records jai_rgm_trx_refs%ROWTYPE;
3843:
3844: ----------------------------------------------------------------------------------------------
3845: --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, end
3846:
3851: select
3852: trx.organization_id,
3853: trx.location_id,
3854: line.service_type_code
3855: from jai_rgm_trx_refs refs,
3856: jai_ar_trxs trx,
3857: jai_ar_trx_tax_lines tax,
3858: jai_ar_trx_lines line
3859: where refs.reference_id = cp_reference_id--r_ar_ref_records.reference_id
4627: p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
4628: p_from_date IN DATE ,
4629: p_to_date IN DATE ,
4630: p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
4631: p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
4632: p_process_flag OUT NOCOPY VARCHAR2 ,
4633: p_process_message OUT NOCOPY VARCHAR2 ,
4634: p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL /*5879769*/
4635: )
5171:
5172:
5173: /****
5174: || Get a sequence number for transaction id
5175: || for the table jai_rgm_trx_refs
5176: *****/
5177: CURSOR cur_get_trx_ref_id
5178: IS
5179: SELECT
5176: *****/
5177: CURSOR cur_get_trx_ref_id
5178: IS
5179: SELECT
5180: jai_rgm_trx_refs_s.nextval
5181: FROM
5182: dual;
5183:
5184: lv_member_name VARCHAR2(80);