[Home] [Help]
63: 1. Modified procedure get_document_details and added cursor c_get_standalone_inv_details, c_get_standalone_org_loc
64: and c_get_standalone_inv_line_tax
65: 2. Modified procedure derrive_doc_from_ref and added code for standalone invoice.
66: 3. Modified procedure extract_rgm_trxs and added code to populate the table
67: jai_trx_repo_extract_gt for standalone invoice.
68:
69: 15. 08-Oct-2009 CSahoo for bug#8965721, File Version 120.19.12010000.7
70: Issue: TST1212.XB2.QA:SERVICE TAX CREDIT NOT ACCOUNTED FOR GOODS TRANSPORT OPERATORS
71: Fix: Modified the cursor c_get_doc_details. Replaced rcv_transactions by jai_rcv_transactions
101: Issue: INDIA SERVICE TAX PROCESSING CONCURRENT DOES NOT PROCESS SERVICE FOR STANDALONE
102: Fixed: Modfiy Cursor c_get_standalone_org_loc, pick up loc/org from parent invoice line.
103: 21. 31-Jan-2011 Bug 10434986
104: Description:
105: + JAI_TRX_REPO_EXTRACT_GT is populated with incorrect Document Line ID
106: (Line Number of Tax Line is inserted into document line id column instead of Item Line Number)
107: + Tax Amount and Line Amount are populated from jai_cmn_document_taxes (Tax Amount column)
108: Hence if partial payment is made, the same is not considered and the whole tax amount
109: is shown in the Service Tax Repository Review window
196: lv_user_id fnd_user.user_id%type default fnd_global.user_id;
197: lv_login_id fnd_logins.login_id%type default fnd_global.login_id;
198:
199: function get_settled_service_type
200: ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
201: , p_document_id jai_trx_repo_extract_gt.document_id%type
202: , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
203: )
204: return varchar2;
197: lv_login_id fnd_logins.login_id%type default fnd_global.login_id;
198:
199: function get_settled_service_type
200: ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
201: , p_document_id jai_trx_repo_extract_gt.document_id%type
202: , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
203: )
204: return varchar2;
205:
198:
199: function get_settled_service_type
200: ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
201: , p_document_id jai_trx_repo_extract_gt.document_id%type
202: , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
203: )
204: return varchar2;
205:
206: procedure set_debug_context
210: lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
211: end set_debug_context;
212: --Add by chong.lei for POT code port begin
213: --Add by Xiao for POT Phase III change, reg bug#12895841, begin
214: FUNCTION get_gl_date( pv_source jai_trx_repo_extract_gt.transaction_source%TYPE,
215: pv_source_trx_type jai_rgm_trx_records.source_trx_type%TYPE,
216: pn_document_id jai_trx_repo_extract_gt.document_id%TYPE,
217: pn_source_document_id jai_rgm_trx_records.source_document_id%TYPE,
218: pn_organization_id NUMBER,
212: --Add by chong.lei for POT code port begin
213: --Add by Xiao for POT Phase III change, reg bug#12895841, begin
214: FUNCTION get_gl_date( pv_source jai_trx_repo_extract_gt.transaction_source%TYPE,
215: pv_source_trx_type jai_rgm_trx_records.source_trx_type%TYPE,
216: pn_document_id jai_trx_repo_extract_gt.document_id%TYPE,
217: pn_source_document_id jai_rgm_trx_records.source_document_id%TYPE,
218: pn_organization_id NUMBER,
219: pn_location_id NUMBER,
220: pv_source_table_name jai_rgm_trx_records.source_table_name%TYPE default NULL,
672: rec_st_transprt_inv_details c_st_transprt_inv_details%rowtype;/* Changes by nprashar , Forward porting from bug 7172723 */
673: rec_st_transprt_party_details c_st_transprt_party_details%rowtype;/* Changes by nprashar , Forward porting from bug 7172723 */
674: lv_service_type_code jai_rgm_trx_records.service_type_code%type;
675:
676: lr_trx_repo_extract jai_trx_repo_extract_gt%rowtype;
677: lv_organization_name hr_organization_units.name%type;
678: lv_location_name hr_locations_all.description%type;
679: ln_reg_id number;
680: --start additions for bug#10085619
942: --Added by Qiong for bug13405553 end
943:
944: jai_cmn_debug_contexts_pkg.print
945: (ln_reg_id
946: , 'Before insert into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
947: 'lr_trx_repo_extract.transaction_repository_id ='|| lr_trx_repo_extract.transaction_repository_id || fnd_global.local_chr(10) ||
948: 'lr_trx_repo_extract.transaction_reference_id ='|| lr_trx_repo_extract.transaction_reference_id || fnd_global.local_chr(10) ||
949: 'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
950: 'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
970: 'lr_trx_repo_extract.inventory_item_id ='|| lr_trx_repo_extract.inventory_item_id || fnd_global.local_chr(10) ||
971: 'lr_trx_repo_extract.party_id ='|| lr_trx_repo_extract.party_id || fnd_global.local_chr(10) ||
972: 'lr_trx_repo_extract.service_type_code ='|| lr_trx_repo_extract.service_type_code
973: );
974: insert into jai_trx_repo_extract_gt
975: (
976: transaction_repository_id
977: ,transaction_reference_id
978: ,transaction_source
1036: ,lr_trx_repo_extract.gl_date --Add by Xiao for POT Phase III, reg bug#12895841. --Added by Chong.Lei for POT code port
1037: ,lr_trx_repo_extract.st_inv_number --Add by Qiong for Advanced Receipts
1038: ,lr_trx_repo_extract.source_trx_type --Add by Qiong for Advanced Receipts
1039: );
1040: jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After insert into jai_trx_repo_extract_gt');
1041: jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'End of Loop -> for r_repo_recs in c_get_repo_recs');
1042:
1043: end loop; --> r_repo_recs in c_get_repo_recs
1044:
1052: , document_id
1053: , document_line_id
1054: , repository_line_id --Add by Qiong for bug#13405553
1055: , transaction_repository_id --Added by Qinglei on 28-Feb-2012 for bug#13741544
1056: from jai_trx_repo_extract_gt gt
1057: --Added by Chong.Lei for POT code port begin
1058: where gt.repository_source in ('AP','AR'
1059: --Add by Xiao for POT Phase III changes, reg bug#12895841 on 1-Aug-2011, begin
1060: , 'AP_REVERSAL'
1262:
1263:
1264: jai_cmn_debug_contexts_pkg.print
1265: (ln_reg_id
1266: , 'Before update into jai_trx_repo_extract_gt' || fnd_global.local_chr(10) ||
1267: 'lr_trx_repo_extract.transaction_source ='|| lr_trx_repo_extract.transaction_source || fnd_global.local_chr(10) ||
1268: 'lr_trx_repo_extract.party_name ='|| lr_trx_repo_extract.party_name || fnd_global.local_chr(10) ||
1269: 'lr_trx_repo_extract.document_number ='|| lr_trx_repo_extract.document_number || fnd_global.local_chr(10) ||
1270: 'lr_trx_repo_extract.document_date ='|| lr_trx_repo_extract.document_date || fnd_global.local_chr(10) ||
1291: 'lr_trx_repo_extract.updatable_flag ='|| lr_trx_repo_extract.updatable_flag || fnd_global.local_chr(10) ||
1292: 'lr_trx_repo_extract.processed_flag ='|| lr_trx_repo_extract.processed_flag
1293: );
1294:
1295: update jai_trx_repo_extract_gt
1296: set transaction_source = lr_trx_repo_extract.transaction_source
1297: , party_name = lr_trx_repo_extract.party_name
1298: , document_number = lr_trx_repo_extract.document_number
1299: , document_date = lr_trx_repo_extract.document_date
1331: --
1332: -- Get organization name for each distinct organization
1333: --
1334:
1335: for r_org in (select distinct organization_id from jai_trx_repo_extract_gt where organization_id is not null)
1336: loop
1337:
1338: jai_cmn_debug_contexts_pkg.print
1339: (ln_reg_id
1349: ,'lv_organization_name='||lv_organization_name
1350: );
1351:
1352:
1353: update jai_trx_repo_extract_gt
1354: set organization_name = lv_organization_name
1355: where organization_id = r_org.organization_id;
1356:
1357: end loop;
1359: --
1360: -- Get location name for each distinct location
1361: --
1362:
1363: for r_loc in (select distinct location_id from jai_trx_repo_extract_gt where location_id is not null )
1364: loop
1365: jai_cmn_debug_contexts_pkg.print
1366: (ln_reg_id
1367: ,'OPEN/FETCH/CLOSE c_get_location_name, r_loc.location_id='||r_loc.location_id
1375: (ln_reg_id
1376: ,'lv_location_name='||lv_location_name
1377: );
1378:
1379: update jai_trx_repo_extract_gt
1380: set location_name = lv_location_name
1381: where location_id = r_loc.location_id;
1382:
1383: end loop;
1406: , p_source_trx_type in varchar2 default NULL
1407: , p_repository_id in varchar2 default NULL
1408: , p_process_message out nocopy varchar2
1409: , p_process_flag out nocopy varchar2
1410: , p_trx_repo_extract in out nocopy jai_trx_repo_extract_gt%rowtype
1411: --added p_taxline_no for bug #10085619
1412: , p_taxline_no in number default null
1413:
1414: )
2001: ----Added by Qiong for bug13597785 Begin
2002: -----------------------------------------------
2003: cursor get_pa_trx_date is
2004: SELECT rct.trx_date document_date
2005: FROM jai_trx_repo_extract_gt gt,
2006: ra_customer_trx_all rct
2007: WHERE gt.document_id = p_document_id
2008: AND gt.document_line_id = p_document_line_id
2009: AND gt.transaction_source = p_document_source
2859: , p_process_flag out nocopy varchar2
2860: , p_process_message out nocopy varchar2
2861: )
2862: is
2863: lr_trx_repo_ext_rec jai_trx_repo_extract_gt%rowtype;
2864: begin
2865:
2866: -- Delegate the call to actual procedure that derrived the document details from refernece
2867: get_doc_from_reference
2885: procedure get_doc_from_reference
2886: ( p_reference_id in number
2887: , p_source_trx_type in varchar2 default NULL
2888: , p_application_id in number default NULL
2889: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
2890: , p_process_flag out nocopy varchar2
2891: , p_process_message out nocopy varchar2
2892: )
2893: is
3148:
3149: cursor c_get_recs_to_update
3150: is
3151: select *
3152: from jai_trx_repo_extract_gt
3153: where processed_flag = jai_constants.NO;
3154:
3155: ln_reg_id number;
3156:
3297: );
3298: --
3299: -- Mark record in global temp table as PROCESSED
3300: --
3301: update jai_trx_repo_extract_gt
3302: set processed_flag = 'Y'
3303: where transaction_repository_id = rec.transaction_repository_id;
3304:
3305: jai_cmn_debug_contexts_pkg.print (ln_reg_id
3302: set processed_flag = 'Y'
3303: where transaction_repository_id = rec.transaction_repository_id;
3304:
3305: jai_cmn_debug_contexts_pkg.print (ln_reg_id
3306: ,'No of rows updated in jai_trx_repo_extract_gt table='||sql%rowcount
3307: );
3308:
3309: end loop;
3310: jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'End loop for C_GET_RECS_TO_UPDATE', jai_cmn_debug_contexts_pkg.summary);
3328: ( p_reference_source in jai_rgm_trx_refs.source%type
3329: , p_source_trx_type in jai_rgm_trx_records.source_trx_type%type default NULL
3330: , p_reference_invoice_id in jai_rgm_trx_refs.invoice_id%type
3331: , p_reference_item_line_id in jai_rgm_trx_refs.item_line_id%type
3332: , p_trx_repo_extract_rec out nocopy jai_trx_repo_extract_gt%rowtype
3333: , p_process_message out nocopy varchar2
3334: , p_process_flag out nocopy varchar2
3335: )
3336: is
3594: end get_service_type_from_ref;
3595: /*------------------------------------------------------------------------------------------------------------*/
3596:
3597: function get_settled_service_type
3598: ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
3599: , p_document_id jai_trx_repo_extract_gt.document_id%type
3600: , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
3601: )
3602: return varchar2 is
3595: /*------------------------------------------------------------------------------------------------------------*/
3596:
3597: function get_settled_service_type
3598: ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
3599: , p_document_id jai_trx_repo_extract_gt.document_id%type
3600: , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
3601: )
3602: return varchar2 is
3603:
3596:
3597: function get_settled_service_type
3598: ( p_transaction_source jai_trx_repo_extract_gt.transaction_source%type
3599: , p_document_id jai_trx_repo_extract_gt.document_id%type
3600: , p_document_line_id jai_trx_repo_extract_gt.document_line_id%type
3601: )
3602: return varchar2 is
3603:
3604: lv_service_type jai_rgm_trx_records.service_type_code%type;