[Home] [Help]
258: 45. 24-Feb-2012 Zhiwei Xin for bug #13777022
259: Fixed: default ln_external_flag as null.
260:
261: 46. 28-Feb-2012 Qinglei for bug#13741544
262: Fixed: Change jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
263: For Advance Receipt reversal transaction.
264:
265: Future Dependencies For the release Of this Object:-
266: (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
312: CURSOR c_get_st_accrual_date(p_organization_id NUMBER, p_location_id NUMBER) IS
313: select to_date(attribute_value, 'DD/MM/YYYY')
314: from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
315: where jrr.regime_id = jrd.regime_id
316: AND jrd.regime_code = jai_constants.service_regime
317: and jrr.organization_id = p_organization_id
318: and jrr.location_id = p_location_id
319: AND jrr.attribute_code = 'EFF_DATE_ST_PT'
320: AND jrr.attribute_type_code = 'OTHERS'
322: AND (NOT EXISTS
323: (select '1'
324: from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
325: where jrr.regime_id = jrd.regime_id
326: AND jrd.regime_code = jai_constants.service_regime
327: and jrr.attribute_code IN 'INV_ORG_CLASSIFICATION'
328: and jrr.attribute_value <> 'ORGANIZATION'
329: and jrr.organization_id = p_organization_id
330: and jrr.location_id = p_location_id)
332: NOT EXISTS
333: (select '1'
334: from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
335: where jrr.regime_id = jrd.regime_id
336: AND jrd.regime_code = jai_constants.service_regime
337: and jrr.attribute_code IN 'SERVICE TYPE'
338: and jrr.attribute_value <> 'OTHER'
339: and jrr.organization_id = p_organization_id
340: and jrr.location_id = p_location_id)
445: FROM
446: JAI_RGM_REGISTRATIONS
447: WHERE
448: regime_id = cp_regime_id AND
449: upper(registration_type) = upper(jai_constants.regn_type_tax_types) AND
450: upper(attribute_code) = upper(cp_tax_type);
451:
452: lv_exists VARCHAR2(2) ;
453: ln_regime_id JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ;
458:
459: /*################################################################################################################
460: || Initialize the variables
461: ################################################################################################################*/
462: p_error_flag := jai_constants.successful ;
463: p_error_message := NULL ;
464: p_regime_id := NULL ;
465:
466: /*################################################################################################################
477: ||As regime has not been defined for this tax type hence error out
478: */
479: CLOSE c_get_regime_id;
480:
481: OPEN c_get_lookup_meaning ( cp_lookup_type => jai_constants.lk_type_tax_type ,
482: cp_lookup_code => p_tax_type_code
483: );
484: FETCH c_get_lookup_meaning INTO lv_meaning;
485: CLOSE c_get_lookup_meaning;
482: cp_lookup_code => p_tax_type_code
483: );
484: FETCH c_get_lookup_meaning INTO lv_meaning;
485: CLOSE c_get_lookup_meaning;
486: p_error_flag := jai_constants.expected_error;
487: p_error_message := 'A regime has to be defined for taxes with tax type as '||lv_meaning;
488: return;
489: END IF;
490: CLOSE c_get_regime_id;
510: CLOSE c_chk_service_tax;
511: /*
512: || Get the meaning from lookup tables for lookup type TAX_TYPE and the lookup code as the current tax type
513: */
514: OPEN c_get_lookup_meaning ( cp_lookup_type => jai_constants.lk_type_tax_type ,
515: cp_lookup_code => p_tax_type_code
516: );
517: FETCH c_get_lookup_meaning INTO lv_meaning;
518: CLOSE c_get_lookup_meaning;
518: CLOSE c_get_lookup_meaning;
519: /*
520: || Get the meaning from lookup tables for lookup type 'JAI_INDIA_TAX_REGIMES' and the lookup code as 'SERVICE'
521: */
522: OPEN c_get_lookup_meaning ( cp_lookup_type => jai_constants.lk_type_ind_tax_rgms ,
523: cp_lookup_code => p_tax_type_code
524: );
525: FETCH c_get_lookup_meaning INTO lv_meaning_rgm;
526: CLOSE c_get_lookup_meaning;
523: cp_lookup_code => p_tax_type_code
524: );
525: FETCH c_get_lookup_meaning INTO lv_meaning_rgm;
526: CLOSE c_get_lookup_meaning;
527: p_error_flag := jai_constants.expected_error;
528: p_error_message := 'A tax type of '|| lv_meaning ||'should be defined for the regime '||lv_meaning_rgm;
529: return;
530: END IF;
531: CLOSE c_chk_service_tax;
533: p_regime_id := ln_regime_id ;
534:
535: EXCEPTION
536: WHEN OTHERS THEN
537: p_error_flag := jai_constants.unexpected_error;
538: p_error_message := 'Unexpected Error Occured in procedure jai_ar_rgm_processing_pkg.get_regime_info - '||substr(sqlerrm,1,300);
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 ,
631:
632: /*
633: || Variable Intialization
634: */
635: p_process_flag := jai_constants.successful ;
636: p_process_message := null ;
637:
638: fnd_file.put_line(fnd_file.LOG,' 2 Variables Initialised ');
639:
637:
638: fnd_file.put_line(fnd_file.LOG,' 2 Variables Initialised ');
639:
640: IF p_total_disc_amount IS NULL THEN
641: p_process_flag := jai_constants.expected_error;
642: p_process_message := 'Service Tax Discounted cannot be NULL ';
643: fnd_file.put_line(fnd_file.LOG,' 3 EXPECTED ERROR - Service Tax Discounted amount cannot be NULL ');
644: return;
645: END IF;
650: OPEN cur_get_receivable_app ;
651: FETCH cur_get_receivable_app into rec_cur_get_receivable_app ;
652: IF CUR_GET_RECEIVABLE_APP%NOTFOUND THEN
653: CLOSE cur_get_receivable_app;
654: p_process_flag := jai_constants.expected_error;
655: p_process_message := 'Receivable Application record with the receivable_application_id -> '||p_receivable_application_id ||' not found ';
656: fnd_file.put_line(fnd_file.LOG,' 4 EXPECTED ERROR -Receivable Application record with the receivable_application_id -> '||p_receivable_application_id ||' not found ');
657: return;
658: END IF;
694:
695: IF CUR_GET_DISC_CCID%NOTFOUND THEN
696: CLOSE cur_get_receivable_app;
697: CLOSE cur_get_disc_ccid;
698: p_process_flag := jai_constants.expected_error ;
699: p_process_message := 'Earned Discount Account Setup not found in ar_receivables_trx_all ';
700: fnd_file.put_line(fnd_file.LOG,' 6 EXPECTED ERROR - Earned Discount Account Setup not found in ar_receivables_trx_all ');
701: return;
702: END IF;
715:
716: IF CUR_GET_DISC_CCID%NOTFOUND THEN
717: CLOSE cur_get_receivable_app;
718: CLOSE cur_get_disc_ccid;
719: p_process_flag := jai_constants.expected_error ;
720: p_process_message := 'UnEarned Discount Account Setup not found in ar_receivables_trx_all ';
721: fnd_file.put_line(fnd_file.LOG,' 8 EXPECTED ERROR - UnEarned Discount Account Setup not found in ar_receivables_trx_all ');
722: return;
723: END IF;
737: fnd_file.put_line(fnd_file.LOG,'********************* 10 END OF PROCEDURE jai_ar_rgm_processing_pkg.GET_AR_TAX_DISC_ACCNT *********************');
738:
739: EXCEPTION
740: WHEN OTHERS THEN
741: p_process_flag := jai_constants.unexpected_error ;
742: p_process_message := 'Unexpeced error occured in procedure get_ar_tax_disc_accnt for receivable_application_id -> '||p_receivable_application_id ||substr(SQLERRM,1,300);
743:
744: END get_ar_tax_disc_accnt ;
745:
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
758: )
769: /******************************
770: ||Cursor Declarations Section
771: *******************************/
772:
773: CURSOR c_fetch_inv_cm_rec ( cp_invoice_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_INV%TYPE ,
774: cp_cm_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_CM%TYPE ,
775: cp_dm_type varchar2, -- Add by Xiao for bug#6773751 on 20-Dec-09
776: cp_service_tax varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE%TYPE ,
777: cp_service_edu_cess varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE_EDU_CESS%TYPE
770: ||Cursor Declarations Section
771: *******************************/
772:
773: CURSOR c_fetch_inv_cm_rec ( cp_invoice_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_INV%TYPE ,
774: cp_cm_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_CM%TYPE ,
775: cp_dm_type varchar2, -- Add by Xiao for bug#6773751 on 20-Dec-09
776: cp_service_tax varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE%TYPE ,
777: cp_service_edu_cess varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE_EDU_CESS%TYPE
778: cp_sh_service_edu_cess JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS%TYPE --Added By Bgowrava for forward porting bug#5989740
772:
773: CURSOR c_fetch_inv_cm_rec ( cp_invoice_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_INV%TYPE ,
774: cp_cm_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_CM%TYPE ,
775: cp_dm_type varchar2, -- Add by Xiao for bug#6773751 on 20-Dec-09
776: cp_service_tax varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE%TYPE ,
777: cp_service_edu_cess varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE_EDU_CESS%TYPE
778: cp_sh_service_edu_cess JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS%TYPE --Added By Bgowrava for forward porting bug#5989740
779: )
780: IS
773: CURSOR c_fetch_inv_cm_rec ( cp_invoice_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_INV%TYPE ,
774: cp_cm_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_CM%TYPE ,
775: cp_dm_type varchar2, -- Add by Xiao for bug#6773751 on 20-Dec-09
776: cp_service_tax varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE%TYPE ,
777: cp_service_edu_cess varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE_EDU_CESS%TYPE
778: cp_sh_service_edu_cess JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS%TYPE --Added By Bgowrava for forward porting bug#5989740
779: )
780: IS
781: SELECT
774: cp_cm_type varchar2, --File.Sql.35 Cbabu jai_constants.AR_INVOICE_TYPE_CM%TYPE ,
775: cp_dm_type varchar2, -- Add by Xiao for bug#6773751 on 20-Dec-09
776: cp_service_tax varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE%TYPE ,
777: cp_service_edu_cess varchar2, --File.Sql.35 Cbabu jai_constants.TAX_TYPE_SERVICE_EDU_CESS%TYPE
778: cp_sh_service_edu_cess JAI_CONSTANTS.TAX_TYPE_SH_SERVICE_EDU_CESS%TYPE --Added By Bgowrava for forward porting bug#5989740
779: )
780: IS
781: SELECT
782: trx.customer_trx_id ,
928: WHERE acra.cash_receipt_id = jcra.cash_receipt_id
929: AND jdt.tax_id = jtc.tax_id
930: AND jtc.tax_type = jrttv.tax_type
931: AND jdt.source_doc_id = jcra.cash_receipt_id
932: AND jrttv.regime_code = jai_constants.service_regime
933: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash
934: AND jcra.CONFIRM_FLAG = 'Y'
935: AND jcra.organization_id = p_organization_id
936: AND jcra.org_id = nvl(p_org_id,jcra.org_id)
929: AND jdt.tax_id = jtc.tax_id
930: AND jtc.tax_type = jrttv.tax_type
931: AND jdt.source_doc_id = jcra.cash_receipt_id
932: AND jrttv.regime_code = jai_constants.service_regime
933: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash
934: AND jcra.CONFIRM_FLAG = 'Y'
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 */
961: /*
962: || Variable Initialization
963: */
964:
965: lv_process_flag := jai_constants.successful ;
966: lv_process_message := NULL ;
967:
968: p_process_flag := lv_process_flag ;
969: p_process_message := lv_process_message ;
970:
971: ln_invoices_processed := 0 ;
972: ln_receipts_processed := 0 ;--Added by Wenqiong for Advanced Receipt
973:
974: FOR rec_c_fetch_inv_cm_rec IN c_fetch_inv_cm_rec ( cp_invoice_type => upper(jai_constants.ar_invoice_type_inv) ,
975: cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
976: cp_dm_type => upper(jai_constants.ar_doc_type_dm) , --Added by Xiao for bug#6773751
977: cp_service_tax => upper(jai_constants.tax_type_service) ,
978: cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
971: ln_invoices_processed := 0 ;
972: ln_receipts_processed := 0 ;--Added by Wenqiong for Advanced Receipt
973:
974: FOR rec_c_fetch_inv_cm_rec IN c_fetch_inv_cm_rec ( cp_invoice_type => upper(jai_constants.ar_invoice_type_inv) ,
975: cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
976: cp_dm_type => upper(jai_constants.ar_doc_type_dm) , --Added by Xiao for bug#6773751
977: cp_service_tax => upper(jai_constants.tax_type_service) ,
978: cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
979: cp_sh_service_edu_cess => upper(jai_constants.tax_type_sh_service_edu_cess) --Added by Bgowrava for forward porting bug#5989740
972: ln_receipts_processed := 0 ;--Added by Wenqiong for Advanced Receipt
973:
974: FOR rec_c_fetch_inv_cm_rec IN c_fetch_inv_cm_rec ( cp_invoice_type => upper(jai_constants.ar_invoice_type_inv) ,
975: cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
976: cp_dm_type => upper(jai_constants.ar_doc_type_dm) , --Added by Xiao for bug#6773751
977: cp_service_tax => upper(jai_constants.tax_type_service) ,
978: cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
979: cp_sh_service_edu_cess => upper(jai_constants.tax_type_sh_service_edu_cess) --Added by Bgowrava for forward porting bug#5989740
980: )
973:
974: FOR rec_c_fetch_inv_cm_rec IN c_fetch_inv_cm_rec ( cp_invoice_type => upper(jai_constants.ar_invoice_type_inv) ,
975: cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
976: cp_dm_type => upper(jai_constants.ar_doc_type_dm) , --Added by Xiao for bug#6773751
977: cp_service_tax => upper(jai_constants.tax_type_service) ,
978: cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
979: cp_sh_service_edu_cess => upper(jai_constants.tax_type_sh_service_edu_cess) --Added by Bgowrava for forward porting bug#5989740
980: )
981: LOOP
974: FOR rec_c_fetch_inv_cm_rec IN c_fetch_inv_cm_rec ( cp_invoice_type => upper(jai_constants.ar_invoice_type_inv) ,
975: cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
976: cp_dm_type => upper(jai_constants.ar_doc_type_dm) , --Added by Xiao for bug#6773751
977: cp_service_tax => upper(jai_constants.tax_type_service) ,
978: cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
979: cp_sh_service_edu_cess => upper(jai_constants.tax_type_sh_service_edu_cess) --Added by Bgowrava for forward porting bug#5989740
980: )
981: LOOP
982:
975: cp_cm_type => upper(jai_constants.ar_invoice_type_cm) ,
976: cp_dm_type => upper(jai_constants.ar_doc_type_dm) , --Added by Xiao for bug#6773751
977: cp_service_tax => upper(jai_constants.tax_type_service) ,
978: cp_service_edu_cess => upper(jai_constants.tax_type_service_edu_cess),
979: cp_sh_service_edu_cess => upper(jai_constants.tax_type_sh_service_edu_cess) --Added by Bgowrava for forward porting bug#5989740
980: )
981: LOOP
982:
983: fnd_file.put_line(fnd_file.LOG,'2 processing record, customer_trx_id '||rec_c_fetch_inv_cm_rec.customer_trx_id
1042: p_tax_type => rec_c_fetch_inv_cm_rec.tax_type ,
1043: p_tax_id => rec_c_fetch_inv_cm_rec.tax_id ,
1044: p_tax_rate => rec_c_fetch_inv_cm_rec.tax_rate ,
1045: p_recoverable_ptg => rec_c_fetch_inv_cm_rec.mod_cr_percentage ,
1046: p_party_type => jai_constants.party_type_customer ,
1047: p_party_id => rec_c_fetch_inv_cm_rec.customer_id ,
1048: p_party_site_id => rec_c_fetch_inv_cm_rec.customer_site_id ,
1049: p_trx_tax_amount => rec_c_fetch_inv_cm_rec.tax_amount ,
1050: p_trx_currency => rec_c_fetch_inv_cm_rec.invoice_currency_code ,
1068: fnd_file.put_line(fnd_file.LOG,'4 returned from procedure jai_cmn_rgm_recording_pkg.insert_reference, lv_process_flag - '||lv_process_flag
1069: ||'lv_process_message - '||lv_process_message);
1070:
1071:
1072: IF lv_process_flag = jai_constants.expected_error OR
1073: lv_process_flag = jai_constants.unexpected_error
1074: THEN
1075: /*
1076: || as Returned status is an error hence:-
1069: ||'lv_process_message - '||lv_process_message);
1070:
1071:
1072: IF lv_process_flag = jai_constants.expected_error OR
1073: lv_process_flag = jai_constants.unexpected_error
1074: THEN
1075: /*
1076: || as Returned status is an error hence:-
1077: ||1. Rollback to save point
1122: p_tax_type => rec_c_fetch_receipt_rec.tax_type ,
1123: p_tax_id => rec_c_fetch_receipt_rec.tax_id ,
1124: p_tax_rate => rec_c_fetch_receipt_rec.tax_rate ,
1125: p_recoverable_ptg => rec_c_fetch_receipt_rec.mod_cr_percentage ,
1126: p_party_type => jai_constants.party_type_customer ,
1127: p_party_id => rec_c_fetch_receipt_rec.customer_id ,
1128: p_party_site_id => rec_c_fetch_receipt_rec.customer_site_use_id ,
1129: p_trx_tax_amount => rec_c_fetch_receipt_rec.tax_amount ,
1130: p_trx_currency => rec_c_fetch_receipt_rec.currency_code ,
1151: fnd_file.put_line(fnd_file.LOG,'2.3 returned from procedure jai_cmn_rgm_recording_pkg.insert_reference, lv_process_flag - '||lv_process_flag
1152: ||'lv_process_message - '||lv_process_message);
1153:
1154:
1155: IF lv_process_flag = jai_constants.expected_error OR
1156: lv_process_flag = jai_constants.unexpected_error
1157: THEN
1158: /*
1159: || as Returned status is an error hence:-
1152: ||'lv_process_message - '||lv_process_message);
1153:
1154:
1155: IF lv_process_flag = jai_constants.expected_error OR
1156: lv_process_flag = jai_constants.unexpected_error
1157: THEN
1158: /*
1159: || as Returned status is an error hence:-
1160: ||1. Rollback to save point
1173: --Added by Wenqiong End
1174: EXCEPTION
1175: WHEN OTHERS THEN
1176:
1177: lv_process_flag := jai_constants.unexpected_error;
1178: lv_process_message := 'Unexpected error occured while processing jai_ar_rgm_processing_pkg.populate_inv_cm_references'||substr(SQLERRM,1,500) ;
1179: ROLLBACK to before_ref_inv_cm;
1180:
1181: END populate_inv_cm_references;
1184: p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
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)
1234: WHERE
1235: trx.customer_trx_id = rgtr.invoice_id AND
1236: trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
1237: trx_types.org_id = trx.org_id AND
1238: upper(trx_types.type) = upper(jai_constants.ar_invoice_type_cm) AND
1239: trx.complete_flag = 'Y'
1240: )
1241: GROUP BY
1242: rgtr.invoice_id
1293:
1294: /*
1295: || Variable Intialization
1296: */
1297: lv_process_flag := jai_constants.successful ;
1298: lv_process_message := null ;
1299:
1300: p_process_flag := lv_process_flag ;
1301: p_process_message := lv_process_message ;
1305:
1306: /*
1307: || Source type would indicate CREDIT MEMO REVERSAL
1308: */
1309: lv_source_trx_type := jai_constants.trx_type_cm_rvs ;
1310:
1311: /*
1312: || Update all the credit memo from reference, reset recovered_amount = 0
1313: || Insert repository records ('CM-CM-APP') corresponding to the above effect
1381: p_process_flag => lv_process_flag,
1382: p_process_message => lv_process_message
1383: );
1384:
1385: IF lv_process_flag <> jai_constants.successful THEN
1386: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
1387: return;
1388: END IF;
1389:
1400: p_location_id => ln_location_id ,/*5879769*/
1401: p_service_type_code => lv_service_type_code ,/*5879769*/
1402: p_source => p_source ,
1403: p_source_trx_type => lv_source_trx_type ,
1404: p_source_table_name => UPPER(jai_constants.repository_name) ,
1405: p_source_document_id => rec_c_get_cm_cm_app_rec.repository_id ,
1406: p_transaction_date => rec_c_get_cm_cm_app_rec.creation_date ,
1407: p_account_name => NULL ,
1408: p_charge_account_id => NULL ,
1428: ----------------------------------------------------------------------------- ,
1429: p_trx_reference_context => lv_attribute_context ,
1430: p_trx_reference2 => rec_c_get_incompleted_cm.cm_customer_trx_id
1431: ---------------------------------------------------------------------------------
1432: , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
1433: );
1434:
1435: fnd_file.put_line(fnd_file.LOG,' 4 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1436:
1434:
1435: fnd_file.put_line(fnd_file.LOG,' 4 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1436:
1437:
1438: IF lv_process_flag = jai_constants.expected_error OR
1439: lv_process_flag = jai_constants.unexpected_error
1440: THEN
1441: /*
1442: || as Returned status is an error hence:-
1435: fnd_file.put_line(fnd_file.LOG,' 4 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1436:
1437:
1438: IF lv_process_flag = jai_constants.expected_error OR
1439: lv_process_flag = jai_constants.unexpected_error
1440: THEN
1441: /*
1442: || as Returned status is an error hence:-
1443: ||1. set the ln_err_cm_customer_trx_id to the cm_customer_Trx_id which errored out
1473: p_recovered_amount => rec_cur_upd_cm_ref.recovered_amount * (-1) ,
1474: p_process_flag => lv_process_flag ,
1475: p_process_message => lv_process_message
1476: );
1477: IF lv_process_flag = jai_constants.expected_error OR
1478: lv_process_flag = jai_constants.unexpected_error
1479: THEN
1480: /*
1481: || as Returned status is an error hence:-
1474: p_process_flag => lv_process_flag ,
1475: p_process_message => lv_process_message
1476: );
1477: IF lv_process_flag = jai_constants.expected_error OR
1478: lv_process_flag = jai_constants.unexpected_error
1479: THEN
1480: /*
1481: || as Returned status is an error hence:-
1482: || Set out variables p_process_flag and p_process_message accordingly
1537: p_process_flag => lv_process_flag,
1538: p_process_message => lv_process_message
1539: );
1540:
1541: IF lv_process_flag <> jai_constants.successful THEN
1542: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
1543: return;
1544: END IF;
1545:
1594: p_trx_reference_context => lv_attribute_context ,
1595: p_trx_reference1 => rec_c_get_cm_inv_app_rec.trx_reference1 ,
1596: p_trx_reference2 => rec_c_get_cm_inv_app_rec.trx_reference2
1597: ---------------------------------------------------------------------------
1598: , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
1599: );
1600:
1601: fnd_file.put_line(fnd_file.LOG,' 10.1 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1602:
1600:
1601: fnd_file.put_line(fnd_file.LOG,' 10.1 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1602:
1603:
1604: IF lv_process_flag = jai_constants.expected_error OR
1605: lv_process_flag = jai_constants.unexpected_error
1606: THEN
1607: /*
1608: || as Returned status is an error hence:-
1601: fnd_file.put_line(fnd_file.LOG,' 10.1 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
1602:
1603:
1604: IF lv_process_flag = jai_constants.expected_error OR
1605: lv_process_flag = jai_constants.unexpected_error
1606: THEN
1607: /*
1608: || as Returned status is an error hence:-
1609: ||1. Rollback to save point
1634: p_recovered_amount => ln_amount ,
1635: p_process_flag => lv_process_flag ,
1636: p_process_message => lv_process_message
1637: );
1638: IF lv_process_flag = jai_constants.expected_error OR
1639: lv_process_flag = jai_constants.unexpected_error
1640: THEN
1641: /*
1642: || as Returned status is an error hence:-
1635: p_process_flag => lv_process_flag ,
1636: p_process_message => lv_process_message
1637: );
1638: IF lv_process_flag = jai_constants.expected_error OR
1639: lv_process_flag = jai_constants.unexpected_error
1640: THEN
1641: /*
1642: || as Returned status is an error hence:-
1643: || Set out variables p_process_flag and p_process_message accordingly
1683: p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
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 )
1735: || 5. Processes DebitMemo for bug#6773751, add by Xiao on 20-Dec-09.
1736: */
1737:
1738: /*Bug 10623735 - Added order by clause on aral.receivable_application_id*/
1739: CURSOR c_get_cm_rec_app ( cp_source_ar varchar2) --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE )
1740: IS
1741: SELECT
1742: aral.customer_trx_id cm_customer_trx_id ,
1743: aral.applied_customer_trx_id inv_customer_trx_id ,
1878: /*
1879: || Get the ref tax lines pertaining to the invoice against which the cash receipt has been applied : -
1880:
1881: */
1882: CURSOR c_get_refinvrec_for_upd ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1883: cp_inv_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
1884: )
1885: IS
1886: SELECT
1901: nvl(recoverable_amount,0) - nvl(discounted_amount,0) > nvl(recovered_amount,0)
1902: FOR UPDATE NOWAIT ;
1903:
1904:
1905: CURSOR c_get_refcmrec_for_upd ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1906: cp_cm_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
1907: )
1908: IS
1909: SELECT
1924:
1925: /*
1926: || Get the total effective CM amount from the CM lines in reference table against which a CM application has to be recovered.
1927: */
1928: CURSOR c_get_cmref_totrd_amt ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
1929: cp_cm_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
1930: )
1931: IS
1932: SELECT
1942: fnd_file.put_line(fnd_file.LOG,'1 Entering procedure : jai_rgm_process_ar.populate_cm_app' );
1943: fnd_file.put_line(fnd_file.LOG,'p_org_id:'||p_org_id );
1944: fnd_file.put_line(fnd_file.LOG,'p_organization_id:'||p_organization_id );
1945:
1946: p_process_flag := jai_constants.successful ;
1947: p_process_message := null ;
1948:
1949: /*****
1950: || Get all the valid CM receivables
1970: ln_inv_tot_tax_amt := null ;
1971: ln_cm_ref_ratio := null ;
1972: ln_cm_ref_upd := null ;
1973: lv_source_trx_type := null ;
1974: lv_process_flag := jai_constants.successful ;
1975: lv_process_message := null ;
1976:
1977: ln_inclusive_total_amt := null ; --added by walton for inclusive tax 29-Nov-07
1978:
2041: IF ln_sign_of_credit_memo > 0 THEN
2042: /*
2043: || +ve credit memo hence CREDIT MEMO REVERSAL
2044: */
2045: lv_source_trx_type := jai_constants.trx_type_cm_rvs ;
2046:
2047: ELSIF ln_sign_of_credit_memo < 0 THEN
2048: /*
2049: || -ve credit memo hence CREDIT MEMO APPLICATION
2047: ELSIF ln_sign_of_credit_memo < 0 THEN
2048: /*
2049: || -ve credit memo hence CREDIT MEMO APPLICATION
2050: */
2051: lv_source_trx_type := jai_constants.trx_type_cm_app ;
2052:
2053: END IF;
2054:
2055: fnd_file.put_line(fnd_file.LOG,'7 credit memo type is lv_source_trx_type-> '||lv_source_trx_type);
2151: p_process_flag => lv_process_flag,
2152: p_process_message => lv_process_message
2153: );
2154:
2155: IF lv_process_flag <> jai_constants.successful THEN
2156: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
2157: return;
2158: END IF;
2159:
2206: p_trx_reference_context => lv_attribute_context ,
2207: p_trx_reference1 => rec_c_get_cm_rec_app.inv_customer_trx_id ,
2208: p_trx_reference2 => rec_c_get_cm_rec_app.cm_customer_trx_id
2209: -----------------------------------------------------------------------------
2210: , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
2211: );
2212:
2213: fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2214:
2212:
2213: fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2214:
2215:
2216: IF lv_process_flag = jai_constants.expected_error OR
2217: lv_process_flag = jai_constants.unexpected_error
2218: THEN
2219: /*
2220: || As Returned status is an error hence:-
2213: fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2214:
2215:
2216: IF lv_process_flag = jai_constants.expected_error OR
2217: lv_process_flag = jai_constants.unexpected_error
2218: THEN
2219: /*
2220: || As Returned status is an error hence:-
2221: ||1. Rollback to save point
2243: p_recovered_amount => ln_amount ,
2244: p_process_flag => lv_process_flag ,
2245: p_process_message => lv_process_message
2246: );
2247: IF lv_process_flag = jai_constants.expected_error OR
2248: lv_process_flag = jai_constants.unexpected_error
2249: THEN
2250: /*
2251: || as Returned status is an error hence:-
2244: p_process_flag => lv_process_flag ,
2245: p_process_message => lv_process_message
2246: );
2247: IF lv_process_flag = jai_constants.expected_error OR
2248: lv_process_flag = jai_constants.unexpected_error
2249: THEN
2250: /*
2251: || as Returned status is an error hence:-
2252: || Set out variables p_process_flag and p_process_message accordingly
2348: p_process_flag => lv_process_flag,
2349: p_process_message => lv_process_message
2350: );
2351:
2352: IF lv_process_flag <> jai_constants.successful THEN
2353: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
2354: return;
2355: END IF;
2356:
2430: ----------------------------------------------------------------------------- ,
2431: p_trx_reference_context => lv_attribute_context ,
2432: p_trx_reference2 => rec_c_get_cm_rec_app.cm_customer_trx_id
2433: -------------------------------------------------------------------------------
2434: , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
2435: );
2436:
2437: fnd_file.put_line(fnd_file.LOG,' 24 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2438:
2436:
2437: fnd_file.put_line(fnd_file.LOG,' 24 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2438:
2439:
2440: IF lv_process_flag = jai_constants.expected_error OR
2441: lv_process_flag = jai_constants.unexpected_error
2442: THEN
2443: /*
2444: || as Returned status is an error hence:-
2437: fnd_file.put_line(fnd_file.LOG,' 24 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
2438:
2439:
2440: IF lv_process_flag = jai_constants.expected_error OR
2441: lv_process_flag = jai_constants.unexpected_error
2442: THEN
2443: /*
2444: || as Returned status is an error hence:-
2445: ||1. Rollback to save point
2478: p_process_message => lv_process_message ,
2479: p_accounting_date => rec_c_get_cm_rec_app.gl_date
2480: );
2481:
2482: IF lv_process_flag = jai_constants.expected_error OR
2483: lv_process_flag = jai_constants.unexpected_error
2484: THEN
2485: ln_receivable_application_id := rec_c_get_cm_rec_app.receivable_application_id;
2486: ROLLBACK to roll_to_last_receivable;
2479: p_accounting_date => rec_c_get_cm_rec_app.gl_date
2480: );
2481:
2482: IF lv_process_flag = jai_constants.expected_error OR
2483: lv_process_flag = jai_constants.unexpected_error
2484: THEN
2485: ln_receivable_application_id := rec_c_get_cm_rec_app.receivable_application_id;
2486: ROLLBACK to roll_to_last_receivable;
2487: p_process_flag := lv_process_flag ;
2510: p_process_message => lv_process_message
2511: );
2512: fnd_file.put_line(fnd_file.LOG,' 21 Returned from jai_cmn_rgm_recording_pkg.update_reference for credit memo references' );
2513:
2514: IF lv_process_flag = jai_constants.expected_error OR
2515: lv_process_flag = jai_constants.unexpected_error
2516: THEN
2517: /*
2518: || As Returned status is an error hence:-
2511: );
2512: fnd_file.put_line(fnd_file.LOG,' 21 Returned from jai_cmn_rgm_recording_pkg.update_reference for credit memo references' );
2513:
2514: IF lv_process_flag = jai_constants.expected_error OR
2515: lv_process_flag = jai_constants.unexpected_error
2516: THEN
2517: /*
2518: || As Returned status is an error hence:-
2519: ||1. Rollback to save point
2543:
2544: EXCEPTION
2545: WHEN OTHERS THEN
2546: fnd_file.put_line(fnd_file.LOG,' 27 In exception section of jai_ar_rgm_processing_pkg.populate_cm_app' );
2547: p_process_flag := jai_constants.unexpected_error;
2548: p_process_message := 'Unexpected error occured while processing jai_ar_rgm_processing_pkg.populate_cm_app'||SQLERRM ;
2549: END populate_cm_app;
2550:
2551:
2553: p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
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 )
2596: || 5.Consider Cash Receipt application for DM ,bug #6773751
2597: */
2598:
2599: /*Bug 10623735 - Added order by clause on aral.receivable_application_id*/
2600: CURSOR c_get_rec_app ( cp_source_ar varchar2) --File.Sql.35 Cbabu ( jai_constants.SOURCE_AR%TYPE )
2601: IS
2602: SELECT
2603: trx.customer_trx_id ,
2604: acrl.cash_receipt_id ,
2633: WHERE
2634: aral.cash_receipt_id = acrl.cash_receipt_id AND
2635: aral.applied_customer_trx_id = trx.customer_trx_id AND
2636: trunc(aral.creation_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
2637: upper(aral.application_type) = upper(jai_constants.ar_cash) AND
2638: upper(aral.status) = upper(jai_constants.ar_status_app) AND
2639: jtrx.organization_id =p_organization_id AND --Added by kunkumar for forward porting to R12
2640: /*nvl(aral.tax_applied,0) <> 0 AND*/--Modified by walton for inclusive tax 29-Nov-07
2641: trx.org_id = nvl(p_org_id,trx.org_id) AND
2634: aral.cash_receipt_id = acrl.cash_receipt_id AND
2635: aral.applied_customer_trx_id = trx.customer_trx_id AND
2636: trunc(aral.creation_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
2637: upper(aral.application_type) = upper(jai_constants.ar_cash) AND
2638: upper(aral.status) = upper(jai_constants.ar_status_app) AND
2639: jtrx.organization_id =p_organization_id AND --Added by kunkumar for forward porting to R12
2640: /*nvl(aral.tax_applied,0) <> 0 AND*/--Modified by walton for inclusive tax 29-Nov-07
2641: trx.org_id = nvl(p_org_id,trx.org_id) AND
2642: trx.complete_flag = 'Y' AND
2640: /*nvl(aral.tax_applied,0) <> 0 AND*/--Modified by walton for inclusive tax 29-Nov-07
2641: trx.org_id = nvl(p_org_id,trx.org_id) AND
2642: trx.complete_flag = 'Y' AND
2643: trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
2644: upper(trx_types.type) IN (upper(jai_constants.ar_invoice_type_inv),upper(jai_constants.ar_invoice_type_cm)
2645: ,upper(jai_constants.ar_doc_type_dm)) AND /* Added ar_doc_type_dm for bug# 6773751 */
2646: trx_types.org_id = trx.org_id AND
2647: trx.customer_trx_id = jtrx.customer_trx_id AND
2648: NOT EXISTS ( SELECT /*A receipt application does not exist in repository */
2641: trx.org_id = nvl(p_org_id,trx.org_id) AND
2642: trx.complete_flag = 'Y' AND
2643: trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
2644: upper(trx_types.type) IN (upper(jai_constants.ar_invoice_type_inv),upper(jai_constants.ar_invoice_type_cm)
2645: ,upper(jai_constants.ar_doc_type_dm)) AND /* Added ar_doc_type_dm for bug# 6773751 */
2646: trx_types.org_id = trx.org_id AND
2647: trx.customer_trx_id = jtrx.customer_trx_id AND
2648: NOT EXISTS ( SELECT /*A receipt application does not exist in repository */
2649: 1
2653: rgtr.source = cp_source_ar AND
2654: rgtr.organization_id = p_organization_id AND/*5879769*/
2655: rgtr.source_table_name = lv_source_table AND
2656: --Added by Qiong for bug13579826 begin
2657: rgtr.source_trx_type in (jai_constants.trx_type_rct_app,jai_constants.trx_type_rct_rvs) AND
2658: --Added by Qiong for bug13579826 end
2659: rgtr.source_document_id = aral.receivable_application_id
2660: ) AND
2661: EXISTS (
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: (
2670: /*Bug 11932841 - Tax applied can be zero if the AR Document has only inclusive Taxes. Replaced > with >= and < with <=*/
2671: ( /*Scope of recovery is possible for cash receipt application */
2744: --------------------------------------------------------------------------------
2745: /*
2746: || Get the ref tax lines pertaining to the invoice against which the cash receipt has been applied : -
2747: */
2748: CURSOR c_get_refrec_for_upd ( cp_source_ar varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
2749: cp_customer_trx_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE
2750: )
2751: IS
2752: SELECT
2830:
2831: /*
2832: ||Initialize the variables
2833: */
2834: p_process_flag := jai_constants.successful ;
2835: p_process_message := null ;
2836:
2837:
2838: /*****
2852: ln_eff_cr_disc_amount := null ;
2853: lv_source_trx_type := null ;
2854: ln_inv_tot_tax_amt := null ;
2855: ln_func_tax_amt := null ; -- Added for Bug 7522584
2856: lv_process_flag := jai_constants.successful ;
2857: lv_process_message := null ;
2858:
2859: ln_inclusive_total_amt := null ; --added by walton for inclusive tax 29-Nov-07
2860:
2971: IF rec_c_get_rec_app.sign_of_cash_receipt > 0 or ln_total_tax_applied >0 THEN --modified by walton for inclusive tax 29-Nov-07
2972: /*
2973: || +ve cash receipt hence RECEIPT APPLICATION
2974: */
2975: lv_source_trx_type := jai_constants.trx_type_rct_app ;
2976: /*the following conditions added by Xiao for bug#6773751*/
2977: if rec_c_get_rec_app.trx_type='INV' then
2978: lv_attribute_context := 'CR-INV-APP' ;
2979: elsif rec_c_get_rec_app.trx_type='DM' then
2986: ELSIF rec_c_get_rec_app.sign_of_cash_receipt < 0 or ln_total_tax_applied <0 THEN --modified by walton for inclusive tax 29-Nov-07
2987: /*
2988: || -ve cash receipt hence RECEIPT REVERSAL/UNAPPLICATION
2989: */
2990: lv_source_trx_type := jai_constants.trx_type_rct_rvs ;
2991: /*the following conditions added by Xiao for bug#6773751*/
2992: if rec_c_get_rec_app.trx_type='INV' then
2993: lv_attribute_context := 'CR-INV-REV' ;
2994: elsif rec_c_get_rec_app.trx_type='DM' then
3174: p_process_flag => lv_process_flag,
3175: p_process_message => lv_process_message
3176: );
3177:
3178: IF lv_process_flag <> jai_constants.successful THEN
3179: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
3180: return;
3181: END IF;
3182:
3261: p_trx_reference_context => lv_attribute_context ,
3262: p_trx_reference1 => rec_c_get_rec_app.customer_trx_id ,
3263: p_trx_reference2 => rec_c_get_rec_app.cash_receipt_id
3264: ----------------------------------------------------------------------------
3265: , p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
3266: );
3267:
3268: fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
3269:
3267:
3268: fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
3269:
3270:
3271: IF lv_process_flag = jai_constants.expected_error OR
3272: lv_process_flag = jai_constants.unexpected_error
3273: THEN
3274: /*
3275: || as Returned status is an error hence:-
3268: fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
3269:
3270:
3271: IF lv_process_flag = jai_constants.expected_error OR
3272: lv_process_flag = jai_constants.unexpected_error
3273: THEN
3274: /*
3275: || as Returned status is an error hence:-
3276: ||1. Rollback to save point
3309: p_process_message => lv_process_message ,
3310: p_accounting_date => rec_c_get_rec_app.gl_date
3311: );
3312:
3313: IF lv_process_flag = jai_constants.expected_error OR
3314: lv_process_flag = jai_constants.unexpected_error
3315: THEN
3316: ln_receivable_application_id := rec_c_get_rec_app.receivable_application_id;
3317: ROLLBACK to roll_to_last_receivable;
3310: p_accounting_date => rec_c_get_rec_app.gl_date
3311: );
3312:
3313: IF lv_process_flag = jai_constants.expected_error OR
3314: lv_process_flag = jai_constants.unexpected_error
3315: THEN
3316: ln_receivable_application_id := rec_c_get_rec_app.receivable_application_id;
3317: ROLLBACK to roll_to_last_receivable;
3318: p_process_flag := lv_process_flag ;
3340: p_process_message => lv_process_message
3341: );
3342:
3343:
3344: IF lv_process_flag = jai_constants.expected_error OR
3345: lv_process_flag = jai_constants.unexpected_error
3346: THEN
3347: /*
3348: || as Returned status is an error hence:-
3341: );
3342:
3343:
3344: IF lv_process_flag = jai_constants.expected_error OR
3345: lv_process_flag = jai_constants.unexpected_error
3346: THEN
3347: /*
3348: || as Returned status is an error hence:-
3349: ||Set out variables p_process_flag and p_process_message accordingly
3372: END LOOP;
3373:
3374: EXCEPTION
3375: WHEN OTHERS THEN
3376: p_process_flag := jai_constants.unexpected_error;
3377: p_process_message := 'Unexpected error occured while processing jai_ar_rgm_processing_pkg.populate_receipt_records'||SQLERRM ;
3378:
3379: END populate_receipt_records;
3380:
3410: WHERE
3411: trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
3412: jtrx.organization_id = p_organization_id AND
3413: trx.org_id = nvl(p_org_id,trx.org_id) AND
3414: upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv,
3415: jai_constants.ar_invoice_type_cm,
3416: jai_constants.ar_doc_type_dm) AND
3417: trx_types.org_id = trx.org_id AND
3418: trx.customer_trx_id = jtrx.customer_trx_id AND
3411: trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
3412: jtrx.organization_id = p_organization_id AND
3413: trx.org_id = nvl(p_org_id,trx.org_id) AND
3414: upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv,
3415: jai_constants.ar_invoice_type_cm,
3416: jai_constants.ar_doc_type_dm) AND
3417: trx_types.org_id = trx.org_id AND
3418: trx.customer_trx_id = jtrx.customer_trx_id AND
3419: trx.trx_date BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
3412: jtrx.organization_id = p_organization_id AND
3413: trx.org_id = nvl(p_org_id,trx.org_id) AND
3414: upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv,
3415: jai_constants.ar_invoice_type_cm,
3416: jai_constants.ar_doc_type_dm) AND
3417: trx_types.org_id = trx.org_id AND
3418: trx.customer_trx_id = jtrx.customer_trx_id AND
3419: trx.trx_date BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
3420: NOT EXISTS ( SELECT 1
3465: /*14121914 - End*/
3466: AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
3467: AND jtrx.organization_id = p_organization_id
3468: AND trx.org_id = nvl(p_org_id,trx.org_id)
3469: AND upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv
3470: , jai_constants.ar_invoice_type_cm
3471: , jai_constants.ar_doc_type_dm
3472: )
3473: AND trx_types.org_id = trx.org_id
3466: AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
3467: AND jtrx.organization_id = p_organization_id
3468: AND trx.org_id = nvl(p_org_id,trx.org_id)
3469: AND upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv
3470: , jai_constants.ar_invoice_type_cm
3471: , jai_constants.ar_doc_type_dm
3472: )
3473: AND trx_types.org_id = trx.org_id
3474: AND trx.customer_trx_id = jtrx.customer_trx_id
3467: AND jtrx.organization_id = p_organization_id
3468: AND trx.org_id = nvl(p_org_id,trx.org_id)
3469: AND upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv
3470: , jai_constants.ar_invoice_type_cm
3471: , jai_constants.ar_doc_type_dm
3472: )
3473: AND trx_types.org_id = trx.org_id
3474: AND trx.customer_trx_id = jtrx.customer_trx_id
3475: --AND trx.trx_date BETWEEN trunc(p_from_date) and trunc(p_to_date) commented by qiong for bug13540741
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,
3541: jai_constants.tax_type_sh_service_edu_cess);
3542:
3543: CURSOR get_total_amt_cur (pn_invoice_id IN NUMBER) IS
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,
3541: jai_constants.tax_type_sh_service_edu_cess);
3542:
3543: CURSOR get_total_amt_cur (pn_invoice_id IN NUMBER) IS
3544: SELECT SUM(total_amount)
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,
3541: jai_constants.tax_type_sh_service_edu_cess);
3542:
3543: CURSOR get_total_amt_cur (pn_invoice_id IN NUMBER) IS
3544: SELECT SUM(total_amount)
3545: FROM jai_ar_trx_lines
3622: FROM jai_rgm_trx_records rgtr
3623: WHERE rgtr.source = 'AR'
3624: AND rgtr.organization_id = p_organization_id
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'
3643: WHERE jacr.cash_receipt_id = cn_cash_receipt_id
3644: AND jdt.source_doc_id = jacr.cash_receipt_id
3645: AND jdt.tax_id = jcta.tax_id
3646: AND jcta.tax_type = jrttv.tax_type
3647: AND jrttv.regime_code = jai_constants.service_regime
3648: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash ;
3649:
3650: CURSOR c_rcpt_ref_records(cp_source_document_id NUMBER, cp_source_doc_line_id NUMBER, cp_source_ar VARCHAR2)
3651: IS
3644: AND jdt.source_doc_id = jacr.cash_receipt_id
3645: AND jdt.tax_id = jcta.tax_id
3646: AND jcta.tax_type = jrttv.tax_type
3647: AND jrttv.regime_code = jai_constants.service_regime
3648: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash ;
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 *
3689: FROM jai_rgm_trx_records rgtr
3690: WHERE rgtr.source = 'AR'
3691: AND rgtr.organization_id = p_organization_id
3692: AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
3693: AND rgtr.source_trx_type = jai_constants.trx_type_adv_rvs
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
3690: WHERE rgtr.source = 'AR'
3691: AND rgtr.organization_id = p_organization_id
3692: AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
3693: AND rgtr.source_trx_type = jai_constants.trx_type_adv_rvs
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
3739: WHERE araa.applied_customer_trx_id = trx.customer_trx_id
3740: AND araa.cash_receipt_id = acra.cash_receipt_id
3741: AND jcra.cash_receipt_id = acra.cash_receipt_id
3742: AND trx.complete_flag = 'Y'
3743: AND upper(araa.application_type) = upper(jai_constants.ar_cash)
3744: AND upper(araa.status) = upper(jai_constants.ar_status_app)
3745: AND jcra.confirm_flag = 'Y'
3746: AND sign (nvl(acra.amount,0)) = sign(nvl(araa.amount_applied,0))-- same means apply, otherwise means unapply
3747: AND jcra.document_type = 'Service'
3740: AND araa.cash_receipt_id = acra.cash_receipt_id
3741: AND jcra.cash_receipt_id = acra.cash_receipt_id
3742: AND trx.complete_flag = 'Y'
3743: AND upper(araa.application_type) = upper(jai_constants.ar_cash)
3744: AND upper(araa.status) = upper(jai_constants.ar_status_app)
3745: AND jcra.confirm_flag = 'Y'
3746: AND sign (nvl(acra.amount,0)) = sign(nvl(araa.amount_applied,0))-- same means apply, otherwise means unapply
3747: AND jcra.document_type = 'Service'
3748: --AND acra.reversal_date IS NULL
3753: FROM jai_rgm_trx_records rgtr
3754: WHERE rgtr.source = 'AR'
3755: AND rgtr.organization_id = p_organization_id
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'
3801: WHERE araa.applied_customer_trx_id = trx.customer_trx_id
3802: AND araa.cash_receipt_id = acra.cash_receipt_id
3803: AND jcra.cash_receipt_id = acra.cash_receipt_id
3804: AND trx.complete_flag = 'Y'
3805: AND upper(araa.application_type) = upper(jai_constants.ar_cash)
3806: --AND upper(araa.status) = upper(jai_constants.ar_status_unapp)
3807: AND jcra.confirm_flag = 'Y'
3808: AND sign (nvl(acra.amount,0)) <> sign(nvl(araa.amount_applied,0))-- same means apply, otherwise means unapply
3809: AND jcra.document_type = 'Service'
3802: AND araa.cash_receipt_id = acra.cash_receipt_id
3803: AND jcra.cash_receipt_id = acra.cash_receipt_id
3804: AND trx.complete_flag = 'Y'
3805: AND upper(araa.application_type) = upper(jai_constants.ar_cash)
3806: --AND upper(araa.status) = upper(jai_constants.ar_status_unapp)
3807: AND jcra.confirm_flag = 'Y'
3808: AND sign (nvl(acra.amount,0)) <> sign(nvl(araa.amount_applied,0))-- same means apply, otherwise means unapply
3809: AND jcra.document_type = 'Service'
3810: --AND acra.reversal_date IS NULL
3815: FROM jai_rgm_trx_records rgtr
3816: WHERE rgtr.source = 'AR'
3817: AND rgtr.organization_id = p_organization_id
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'
3895: p_process_flag => lv_process_flag,
3896: p_process_message => lv_process_message
3897: );
3898:
3899: IF lv_process_flag <> jai_constants.successful THEN
3900: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
3901: return;
3902: END IF; */
3903: ----------------------------------------------------------------------------------------------
3922: p_organization_id => ln_organization_id ,
3923: p_location_id => ln_location_id ,
3924: p_service_type_code => lv_service_type_code ,
3925: p_source => p_source ,
3926: p_source_trx_type => jai_constants.trx_event_inv_save ,
3927: p_source_table_name => UPPER(jai_constants.tname_cus_trx_lines) ,
3928: --p_source_document_id => r_ar_ref_records.invoice_id --Commented by zhiwei.hou for POT code port
3929: p_source_document_id => r_ar_ref_records.line_id, --Modified by Xiao for POT bug#12533434 ,
3930: p_transaction_date => r_ar_transactions.gl_date, --Changed by Qiong for bug13540741 ,
3923: p_location_id => ln_location_id ,
3924: p_service_type_code => lv_service_type_code ,
3925: p_source => p_source ,
3926: p_source_trx_type => jai_constants.trx_event_inv_save ,
3927: p_source_table_name => UPPER(jai_constants.tname_cus_trx_lines) ,
3928: --p_source_document_id => r_ar_ref_records.invoice_id --Commented by zhiwei.hou for POT code port
3929: p_source_document_id => r_ar_ref_records.line_id, --Modified by Xiao for POT bug#12533434 ,
3930: p_transaction_date => r_ar_transactions.gl_date, --Changed by Qiong for bug13540741 ,
3931: p_account_name => NULL ,
3946: p_curr_conv_date => r_ar_ref_records.curr_conv_date ,
3947: p_curr_conv_type => NULL ,
3948: p_curr_conv_rate => r_ar_ref_records.curr_conv_rate ,
3949: p_trx_amount => r_ar_ref_records.trx_tax_amount ,
3950: p_accntg_required_flag => jai_constants.no,
3951: p_accrual_basis => 'Y' ,--Add by Xiao for pot change, reg bug#12533434
3952: p_invoice_no => r_ar_transactions.st_inv_number --Added by Qiong for Advanced Receipts
3953: );
3954: IF lv_process_flag = jai_constants.expected_error OR
3950: p_accntg_required_flag => jai_constants.no,
3951: p_accrual_basis => 'Y' ,--Add by Xiao for pot change, reg bug#12533434
3952: p_invoice_no => r_ar_transactions.st_inv_number --Added by Qiong for Advanced Receipts
3953: );
3954: IF lv_process_flag = jai_constants.expected_error OR
3955: lv_process_flag = jai_constants.unexpected_error THEN
3956: fnd_file.put_line( fnd_file.log, '5.1 ERROR IN CALL TO jai_ar_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
3957: ||', lv_process_message'||lv_process_message);
3958: p_process_flag := lv_process_flag ;
3951: p_accrual_basis => 'Y' ,--Add by Xiao for pot change, reg bug#12533434
3952: p_invoice_no => r_ar_transactions.st_inv_number --Added by Qiong for Advanced Receipts
3953: );
3954: IF lv_process_flag = jai_constants.expected_error OR
3955: lv_process_flag = jai_constants.unexpected_error THEN
3956: fnd_file.put_line( fnd_file.log, '5.1 ERROR IN CALL TO jai_ar_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
3957: ||', lv_process_message'||lv_process_message);
3958: p_process_flag := lv_process_flag ;
3959: p_process_message := lv_process_message ;
3965: p_recovered_amount => r_ar_ref_records.trx_tax_amount ,
3966: p_process_flag => lv_process_flag ,
3967: p_process_message => lv_process_message
3968: );
3969: IF lv_process_flag = jai_constants.expected_error OR
3970: lv_process_flag = jai_constants.unexpected_error THEN
3971: fnd_file.put_line( fnd_file.log, '6.1 ERROR IN CALL TO jai_ar_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
3972: ||', lv_process_message'||lv_process_message);
3973: p_process_flag := lv_process_flag ;
3966: p_process_flag => lv_process_flag ,
3967: p_process_message => lv_process_message
3968: );
3969: IF lv_process_flag = jai_constants.expected_error OR
3970: lv_process_flag = jai_constants.unexpected_error THEN
3971: fnd_file.put_line( fnd_file.log, '6.1 ERROR IN CALL TO jai_ar_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
3972: ||', lv_process_message'||lv_process_message);
3973: p_process_flag := lv_process_flag ;
3974: p_process_message := lv_process_message ;
4003: p_service_type_code => lv_service_type_code,
4004: p_process_flag => lv_process_flag,
4005: p_process_message => lv_process_message);
4006:
4007: IF lv_process_flag <> jai_constants.successful THEN
4008: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4009: RETURN;
4010: END IF;
4011:
4009: RETURN;
4010: END IF;
4011:
4012: ln_tax_amount := round(ln_reversal_amount * rec_reversal_entries.tax_amt/ln_total_rec_amt,
4013: jai_constants.service_rgm_rnd_factor);
4014:
4015: IF nvl(ln_tax_amount, 0) > 0 THEN
4016: jai_cmn_rgm_recording_pkg.insert_repository_entry(
4017: p_repository_id => ln_repository_id,
4044: p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
4045: p_curr_conv_type => NULL,
4046: p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
4047: p_trx_amount => ln_tax_amount,--lr_ar_ref_records.trx_tax_amount,
4048: p_accntg_required_flag => jai_constants.no,
4049: p_accrual_basis => 'Y');
4050:
4051: IF lv_process_flag = jai_constants.expected_error OR
4052: lv_process_flag = jai_constants.unexpected_error THEN
4047: p_trx_amount => ln_tax_amount,--lr_ar_ref_records.trx_tax_amount,
4048: p_accntg_required_flag => jai_constants.no,
4049: p_accrual_basis => 'Y');
4050:
4051: IF lv_process_flag = jai_constants.expected_error OR
4052: lv_process_flag = jai_constants.unexpected_error THEN
4053: fnd_file.put_line( fnd_file.log,
4054: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4055: ||', lv_process_message'||lv_process_message);
4048: p_accntg_required_flag => jai_constants.no,
4049: p_accrual_basis => 'Y');
4050:
4051: IF lv_process_flag = jai_constants.expected_error OR
4052: lv_process_flag = jai_constants.unexpected_error THEN
4053: fnd_file.put_line( fnd_file.log,
4054: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4055: ||', lv_process_message'||lv_process_message);
4056: p_process_flag := lv_process_flag;
4088: p_service_type_code => lv_service_type_code,
4089: p_process_flag => lv_process_flag,
4090: p_process_message => lv_process_message);
4091:
4092: IF lv_process_flag <> jai_constants.successful THEN
4093: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4094: RETURN;
4095: END IF;
4096:
4095: END IF;
4096:
4097: ld_accounting_date := rec_claim_line.gl_date;
4098: ln_tax_amount := round(rec_claim_line.claim_amt * rec_claim_entries.tax_amt/ln_total_amount,
4099: jai_constants.service_rgm_rnd_factor);
4100:
4101: IF nvl(ln_tax_amount, 0) > 0 THEN
4102: jai_cmn_rgm_recording_pkg.insert_repository_entry(
4103: p_repository_id => ln_repository_id,
4130: p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
4131: p_curr_conv_type => NULL,
4132: p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
4133: p_trx_amount => ln_tax_amount,--lr_ar_ref_records.trx_tax_amount,
4134: p_accntg_required_flag => jai_constants.no,
4135: p_accrual_basis => 'Y');
4136:
4137: IF lv_process_flag = jai_constants.expected_error OR
4138: lv_process_flag = jai_constants.unexpected_error THEN
4133: p_trx_amount => ln_tax_amount,--lr_ar_ref_records.trx_tax_amount,
4134: p_accntg_required_flag => jai_constants.no,
4135: p_accrual_basis => 'Y');
4136:
4137: IF lv_process_flag = jai_constants.expected_error OR
4138: lv_process_flag = jai_constants.unexpected_error THEN
4139: fnd_file.put_line( fnd_file.log,
4140: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4141: ||', lv_process_message'||lv_process_message);
4134: p_accntg_required_flag => jai_constants.no,
4135: p_accrual_basis => 'Y');
4136:
4137: IF lv_process_flag = jai_constants.expected_error OR
4138: lv_process_flag = jai_constants.unexpected_error THEN
4139: fnd_file.put_line( fnd_file.log,
4140: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4141: ||', lv_process_message'||lv_process_message);
4142: p_process_flag := lv_process_flag;
4169: CLOSE c_rcpt_ref_records;
4170:
4171:
4172: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4173: p_source_trx_type => jai_constants.trx_type_adv_rcpts,
4174: p_organization_id => ln_organization_id,
4175: p_location_id => ln_location_id,
4176: p_service_type_code => lv_service_type_code,
4177: p_process_flag => lv_process_flag,
4176: p_service_type_code => lv_service_type_code,
4177: p_process_flag => lv_process_flag,
4178: p_process_message => lv_process_message);
4179:
4180: IF lv_process_flag <> jai_constants.successful THEN
4181: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4182: RETURN;
4183: END IF;
4184:
4194: p_organization_id => ln_organization_id,
4195: p_location_id => ln_location_id,
4196: p_service_type_code => lv_service_type_code,
4197: p_source => 'AR',
4198: p_source_trx_type => jai_constants.trx_type_adv_rcpts,
4199: p_source_table_name => 'AR_CASH_RECEIPTS_ALL',
4200: p_source_document_id => rec_c_ar_receipts.cash_receipt_id,
4201: p_transaction_date => ld_gl_date,
4202: p_account_name => NULL,
4217: p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
4218: p_curr_conv_type => rec_c_ar_receipts.exchange_rate_type,
4219: p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
4220: p_trx_amount => ln_trx_tax_amount,
4221: p_accntg_required_flag => jai_constants.no,
4222: p_accrual_basis => 'Y');
4223:
4224: IF lv_process_flag = jai_constants.expected_error OR
4225: lv_process_flag = jai_constants.unexpected_error THEN
4220: p_trx_amount => ln_trx_tax_amount,
4221: p_accntg_required_flag => jai_constants.no,
4222: p_accrual_basis => 'Y');
4223:
4224: IF lv_process_flag = jai_constants.expected_error OR
4225: lv_process_flag = jai_constants.unexpected_error THEN
4226: fnd_file.put_line( fnd_file.log,
4227: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4228: ||', lv_process_message'||lv_process_message);
4221: p_accntg_required_flag => jai_constants.no,
4222: p_accrual_basis => 'Y');
4223:
4224: IF lv_process_flag = jai_constants.expected_error OR
4225: lv_process_flag = jai_constants.unexpected_error THEN
4226: fnd_file.put_line( fnd_file.log,
4227: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4228: ||', lv_process_message'||lv_process_message);
4229: p_process_flag := lv_process_flag;
4259: ln_trx_tax_amount := - ABS(lr_ar_ref_records.TRX_TAX_AMOUNT);
4260:
4261:
4262: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4263: p_source_trx_type => jai_constants.trx_type_adv_rvs,
4264: --Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
4265: --by Qinglei on 28-Dec-2012 for bug#13741544
4266: p_organization_id => ln_organization_id,
4267: p_location_id => ln_location_id,
4260:
4261:
4262: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4263: p_source_trx_type => jai_constants.trx_type_adv_rvs,
4264: --Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
4265: --by Qinglei on 28-Dec-2012 for bug#13741544
4266: p_organization_id => ln_organization_id,
4267: p_location_id => ln_location_id,
4268: p_service_type_code => lv_service_type_code,
4268: p_service_type_code => lv_service_type_code,
4269: p_process_flag => lv_process_flag,
4270: p_process_message => lv_process_message);
4271:
4272: IF lv_process_flag <> jai_constants.successful THEN
4273: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4274: RETURN;
4275: END IF;
4276:
4284: p_organization_id => ln_organization_id,
4285: p_location_id => ln_location_id,
4286: p_service_type_code => lv_service_type_code,
4287: p_source => 'AR',
4288: p_source_trx_type => jai_constants.trx_type_adv_rvs,
4289: --Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
4290: --by Qinglei on 28-Dec-2012 for bug#13741544
4291: p_source_table_name => 'AR_CASH_RECEIPTS_ALL',
4292: p_source_document_id => rec_c_receipts_reversal.cash_receipt_id,
4285: p_location_id => ln_location_id,
4286: p_service_type_code => lv_service_type_code,
4287: p_source => 'AR',
4288: p_source_trx_type => jai_constants.trx_type_adv_rvs,
4289: --Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
4290: --by Qinglei on 28-Dec-2012 for bug#13741544
4291: p_source_table_name => 'AR_CASH_RECEIPTS_ALL',
4292: p_source_document_id => rec_c_receipts_reversal.cash_receipt_id,
4293: p_transaction_date => ld_gl_date,
4309: p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
4310: p_curr_conv_type => rec_c_receipts_reversal.exchange_rate_type,
4311: p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
4312: p_trx_amount => ln_trx_tax_amount,
4313: p_accntg_required_flag => jai_constants.no,
4314: p_accrual_basis => 'Y');
4315:
4316: IF lv_process_flag = jai_constants.expected_error OR
4317: lv_process_flag = jai_constants.unexpected_error THEN
4312: p_trx_amount => ln_trx_tax_amount,
4313: p_accntg_required_flag => jai_constants.no,
4314: p_accrual_basis => 'Y');
4315:
4316: IF lv_process_flag = jai_constants.expected_error OR
4317: lv_process_flag = jai_constants.unexpected_error THEN
4318: fnd_file.put_line( fnd_file.log,
4319: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4320: ||', lv_process_message'||lv_process_message);
4313: p_accntg_required_flag => jai_constants.no,
4314: p_accrual_basis => 'Y');
4315:
4316: IF lv_process_flag = jai_constants.expected_error OR
4317: lv_process_flag = jai_constants.unexpected_error THEN
4318: fnd_file.put_line( fnd_file.log,
4319: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4320: ||', lv_process_message'||lv_process_message);
4321: p_process_flag := lv_process_flag;
4358: */
4359: ln_tax_amount := -ROUND(ABS(rec_receipt_entries.func_tax_amt
4360: * (rec_c_receipts_application.AMOUNT_APPLIED_FROM/
4361: (rec_c_receipts_application.AMOUNT * NVL(ln_converted_rate,1)))),
4362: jai_constants.service_rgm_rnd_factor);
4363: --Modified by Qinglei on 05-Jan-2012 for bug#13556198
4364: --Added rounding factor by Qinglei on 13-Jan-2012 for bug#13557031
4365: ln_trx_tax_amount := -ROUND(ABS(rec_receipt_entries.tax_amt
4366: * (rec_c_receipts_application.AMOUNT_APPLIED_FROM/
4364: --Added rounding factor by Qinglei on 13-Jan-2012 for bug#13557031
4365: ln_trx_tax_amount := -ROUND(ABS(rec_receipt_entries.tax_amt
4366: * (rec_c_receipts_application.AMOUNT_APPLIED_FROM/
4367: (rec_c_receipts_application.AMOUNT * NVL(ln_converted_rate,1)))),
4368: jai_constants.service_rgm_rnd_factor);
4369: --Modified by Qinglei on 05-Jan-2012 for bug#13556198
4370: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4371: p_source_trx_type => jai_constants.trx_type_adv_app,
4372: p_application_id => rec_c_receipts_application.receivable_application_id,
4367: (rec_c_receipts_application.AMOUNT * NVL(ln_converted_rate,1)))),
4368: jai_constants.service_rgm_rnd_factor);
4369: --Modified by Qinglei on 05-Jan-2012 for bug#13556198
4370: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4371: p_source_trx_type => jai_constants.trx_type_adv_app,
4372: p_application_id => rec_c_receipts_application.receivable_application_id,
4373: p_organization_id => ln_organization_id,
4374: p_location_id => ln_location_id,
4375: p_service_type_code => lv_service_type_code,
4375: p_service_type_code => lv_service_type_code,
4376: p_process_flag => lv_process_flag,
4377: p_process_message => lv_process_message);
4378:
4379: IF lv_process_flag <> jai_constants.successful THEN
4380: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4381: RETURN;
4382: END IF;
4383:
4391: p_organization_id => ln_organization_id,
4392: p_location_id => ln_location_id,
4393: p_service_type_code => lv_service_type_code,
4394: p_source => 'AR',
4395: p_source_trx_type => jai_constants.trx_type_adv_app,
4396: p_source_table_name => 'AR_RECEIVABLE_APPLICATIONS_ALL',
4397: p_source_document_id => rec_c_receipts_application.receivable_application_id,
4398: p_transaction_date => rec_c_receipts_application.gl_date,
4399: p_account_name => NULL,
4414: p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
4415: p_curr_conv_type => rec_c_receipts_application.receipt_exchange_rate_type,
4416: p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
4417: p_trx_amount => ln_trx_tax_amount,
4418: p_accntg_required_flag => jai_constants.no,
4419: p_accrual_basis => 'Y');
4420:
4421: IF lv_process_flag = jai_constants.expected_error OR
4422: lv_process_flag = jai_constants.unexpected_error THEN
4417: p_trx_amount => ln_trx_tax_amount,
4418: p_accntg_required_flag => jai_constants.no,
4419: p_accrual_basis => 'Y');
4420:
4421: IF lv_process_flag = jai_constants.expected_error OR
4422: lv_process_flag = jai_constants.unexpected_error THEN
4423: fnd_file.put_line( fnd_file.log,
4424: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4425: ||', lv_process_message'||lv_process_message);
4418: p_accntg_required_flag => jai_constants.no,
4419: p_accrual_basis => 'Y');
4420:
4421: IF lv_process_flag = jai_constants.expected_error OR
4422: lv_process_flag = jai_constants.unexpected_error THEN
4423: fnd_file.put_line( fnd_file.log,
4424: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4425: ||', lv_process_message'||lv_process_message);
4426: p_process_flag := lv_process_flag;
4463: */
4464: ln_tax_amount := ROUND(ABS(rec_receipt_entries.func_tax_amt
4465: * (rec_c_receipts_unapplication.AMOUNT_APPLIED_FROM/
4466: (rec_c_receipts_unapplication.AMOUNT * NVL(ln_converted_rate,1)))),
4467: jai_constants.service_rgm_rnd_factor);
4468: --Modified by Qinglei on 05-Jan-2012 for bug#13556198
4469: --Added rounding factor by Qinglei on 13-Jan-2012 for bug#13557031
4470: ln_trx_tax_amount := ROUND(ABS(rec_receipt_entries.tax_amt
4471: * (rec_c_receipts_unapplication.AMOUNT_APPLIED_FROM/
4469: --Added rounding factor by Qinglei on 13-Jan-2012 for bug#13557031
4470: ln_trx_tax_amount := ROUND(ABS(rec_receipt_entries.tax_amt
4471: * (rec_c_receipts_unapplication.AMOUNT_APPLIED_FROM/
4472: (rec_c_receipts_unapplication.AMOUNT * NVL(ln_converted_rate,1)))),
4473: jai_constants.service_rgm_rnd_factor);
4474: --Modified by Qinglei on 05-Jan-2012 for bug#13556198
4475: --Added rounding factor by Qinglei on 13-Jan-2012 for bug#13557031
4476:
4477: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4474: --Modified by Qinglei on 05-Jan-2012 for bug#13556198
4475: --Added rounding factor by Qinglei on 13-Jan-2012 for bug#13557031
4476:
4477: jai_trx_repo_extract_pkg.get_doc_from_reference(p_reference_id => lr_ar_ref_records.reference_id,
4478: p_source_trx_type => jai_constants.trx_type_adv_unapp,
4479: p_application_id => rec_c_receipts_unapplication.receivable_application_id,
4480: p_organization_id => ln_organization_id,
4481: p_location_id => ln_location_id,
4482: p_service_type_code => lv_service_type_code,
4482: p_service_type_code => lv_service_type_code,
4483: p_process_flag => lv_process_flag,
4484: p_process_message => lv_process_message);
4485:
4486: IF lv_process_flag <> jai_constants.successful THEN
4487: FND_FILE.put_line(fnd_file.log, 'Error Flag:'||lv_process_flag||' Error Message:'||lv_process_message);
4488: RETURN;
4489: END IF;
4490:
4498: p_organization_id => ln_organization_id,
4499: p_location_id => ln_location_id,
4500: p_service_type_code => lv_service_type_code,
4501: p_source => 'AR',
4502: p_source_trx_type => jai_constants.trx_type_adv_unapp,
4503: p_source_table_name => 'AR_RECEIVABLE_APPLICATIONS_ALL',
4504: p_source_document_id => rec_c_receipts_unapplication.receivable_application_id,
4505: p_transaction_date => rec_c_receipts_unapplication.gl_date,
4506: p_account_name => NULL,
4521: p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
4522: p_curr_conv_type => rec_c_receipts_unapplication.receipt_exchange_rate_type,
4523: p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
4524: p_trx_amount => ln_trx_tax_amount,
4525: p_accntg_required_flag => jai_constants.no,
4526: p_accrual_basis => 'Y');
4527:
4528: IF lv_process_flag = jai_constants.expected_error OR
4529: lv_process_flag = jai_constants.unexpected_error THEN
4524: p_trx_amount => ln_trx_tax_amount,
4525: p_accntg_required_flag => jai_constants.no,
4526: p_accrual_basis => 'Y');
4527:
4528: IF lv_process_flag = jai_constants.expected_error OR
4529: lv_process_flag = jai_constants.unexpected_error THEN
4530: fnd_file.put_line( fnd_file.log,
4531: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4532: ||', lv_process_message'||lv_process_message);
4525: p_accntg_required_flag => jai_constants.no,
4526: p_accrual_basis => 'Y');
4527:
4528: IF lv_process_flag = jai_constants.expected_error OR
4529: lv_process_flag = jai_constants.unexpected_error THEN
4530: fnd_file.put_line( fnd_file.log,
4531: '5.1 ERROR IN CALL TO jai_cmn_rgm_processing_pkg.populate_repository - lv_process_flag '||lv_process_flag
4532: ||', lv_process_message'||lv_process_message);
4533: p_process_flag := lv_process_flag;
4634: p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL /*5879769*/
4635: )
4636: IS
4637:
4638: lv_source_ar varchar2(2); --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ;
4639: lv_process_flag VARCHAR2(2) ;
4640: lv_process_message VARCHAR2(1996) ;
4641: lv_organization_id jai_rgm_parties.organization_id%type;
4642: /*Bug 11821537 - Start*/
4646: SELECT to_date(attribute_value, 'DD/MM/YYYY')
4647: FROM jai_rgm_registrations
4648: WHERE regime_id = (select regime_id
4649: from JAI_RGM_DEFINITIONS
4650: where regime_code = jai_constants.service_regime)
4651: AND attribute_code = 'EFF_DATE_ST_PT'
4652: AND attribute_type_code = 'OTHERS'
4653: AND registration_type = 'OTHERS';
4654: */
4661: /*########################################################################################################
4662: || VARIABLES INITIALIZATION
4663: ########################################################################################################*/
4664:
4665: lv_source_ar := jai_constants.source_ar ;
4666:
4667: lv_process_flag := jai_constants.successful ;
4668: lv_process_message := null ;
4669:
4663: ########################################################################################################*/
4664:
4665: lv_source_ar := jai_constants.source_ar ;
4666:
4667: lv_process_flag := jai_constants.successful ;
4668: lv_process_message := null ;
4669:
4670: p_process_flag := lv_process_flag ;
4671: p_process_message := lv_process_message ;
4681: --as ST processor should process for both Accrual/Cash basis.
4682:
4683: IF ld_st_accrual_date > p_from_date OR ld_st_accrual_date > p_to_date THEN
4684: fnd_file.put_line( fnd_file.log, '1.1 From and To Date parameters for Service Tax Processing cannot be less than Effective Date for
4685: Service Tax Point of Taxation in Regime Registration - lv_process_flag '|| jai_constants.unexpected_error
4686: ||', lv_process_message Verify Date Parameters');
4687: p_process_flag := jai_constants.unexpected_error ;
4688: p_process_message := 'Verify Date Parameters' ;
4689: END IF;*/
4683: IF ld_st_accrual_date > p_from_date OR ld_st_accrual_date > p_to_date THEN
4684: fnd_file.put_line( fnd_file.log, '1.1 From and To Date parameters for Service Tax Processing cannot be less than Effective Date for
4685: Service Tax Point of Taxation in Regime Registration - lv_process_flag '|| jai_constants.unexpected_error
4686: ||', lv_process_message Verify Date Parameters');
4687: p_process_flag := jai_constants.unexpected_error ;
4688: p_process_message := 'Verify Date Parameters' ;
4689: END IF;*/
4690: /*Bug 11821537 - End*/
4691: fnd_file.put_line(fnd_file.LOG,'2 i/p is p_regime_id ->'||p_regime_id
4725: pn_organization_id => p_organization_id);
4726: --Add by Xiao for POT Phase III, reg bug#12895841, end
4727:
4728:
4729: IF lv_process_flag = jai_constants.expected_error OR
4730: lv_process_flag = jai_constants.unexpected_error
4731: THEN
4732: /*
4733: || As Returned status is an error hence:-
4726: --Add by Xiao for POT Phase III, reg bug#12895841, end
4727:
4728:
4729: IF lv_process_flag = jai_constants.expected_error OR
4730: lv_process_flag = jai_constants.unexpected_error
4731: THEN
4732: /*
4733: || As Returned status is an error hence:-
4734: || Set out variables p_process_flag and p_process_message accordingly
4756: p_process_flag => lv_process_flag ,
4757: p_process_message => lv_process_message
4758: );
4759:
4760: IF lv_process_flag = jai_constants.expected_error OR
4761: lv_process_flag = jai_constants.unexpected_error
4762: THEN
4763: /*
4764: || As Returned status is an error hence:-
4757: p_process_message => lv_process_message
4758: );
4759:
4760: IF lv_process_flag = jai_constants.expected_error OR
4761: lv_process_flag = jai_constants.unexpected_error
4762: THEN
4763: /*
4764: || As Returned status is an error hence:-
4765: || Set out variables p_process_flag and p_process_message accordingly
4797: p_process_flag => lv_process_flag ,
4798: p_process_message => lv_process_message
4799: );
4800:
4801: IF lv_process_flag = jai_constants.expected_error OR
4802: lv_process_flag = jai_constants.unexpected_error
4803: THEN
4804:
4805: fnd_file.put_line( fnd_file.log, '7 ERROR IN CALL TO jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM - lv_process_flag '||lv_process_flag
4798: p_process_message => lv_process_message
4799: );
4800:
4801: IF lv_process_flag = jai_constants.expected_error OR
4802: lv_process_flag = jai_constants.unexpected_error
4803: THEN
4804:
4805: fnd_file.put_line( fnd_file.log, '7 ERROR IN CALL TO jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM - lv_process_flag '||lv_process_flag
4806: ||', lv_process_message'||lv_process_message);
4832: p_process_message => lv_process_message
4833: );
4834:
4835:
4836: IF lv_process_flag = jai_constants.expected_error OR
4837: lv_process_flag = jai_constants.unexpected_error
4838: THEN
4839:
4840: fnd_file.put_line( fnd_file.log, '10 error in call to jai_ar_rgm_processing_pkg.populate_cm_app - lv_process_flag '||lv_process_flag
4833: );
4834:
4835:
4836: IF lv_process_flag = jai_constants.expected_error OR
4837: lv_process_flag = jai_constants.unexpected_error
4838: THEN
4839:
4840: fnd_file.put_line( fnd_file.log, '10 error in call to jai_ar_rgm_processing_pkg.populate_cm_app - lv_process_flag '||lv_process_flag
4841: ||', lv_process_message'||lv_process_message);
4866: p_process_flag => lv_process_flag ,
4867: p_process_message => lv_process_message
4868: );
4869:
4870: IF lv_process_flag = jai_constants.expected_error OR
4871: lv_process_flag = jai_constants.unexpected_error
4872: THEN
4873:
4874: fnd_file.put_line( fnd_file.log, '13 ERROR IN CALL TO jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS - lv_process_flag '||lv_process_flag
4867: p_process_message => lv_process_message
4868: );
4869:
4870: IF lv_process_flag = jai_constants.expected_error OR
4871: lv_process_flag = jai_constants.unexpected_error
4872: THEN
4873:
4874: fnd_file.put_line( fnd_file.log, '13 ERROR IN CALL TO jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS - lv_process_flag '||lv_process_flag
4875: ||', lv_process_message'||lv_process_message);
4884: fnd_file.put_line(fnd_file.LOG,'############################## 15 END OF PROCEDURE TO jai_ar_rgm_processing_pkg.PROCESS_RECORDS - ##############################');
4885:
4886: EXCEPTION
4887: WHEN OTHERS THEN
4888: lv_process_flag := jai_constants.unexpected_error;
4889: lv_process_message := ' 16 Unexpected error occured while processing jai_ar_rgm_processing_pkg.process_records'||SQLERRM ;
4890:
4891: END process_records;
4892:
4921: WHERE cash_receipt_id = cp_cash_receipt_id;
4922:
4923: BEGIN
4924:
4925: lv_process_flag := jai_constants.successful;
4926: lv_process_message := NULL;
4927:
4928: p_process_flag := lv_process_flag;
4929: p_process_message := lv_process_message;
4927:
4928: p_process_flag := lv_process_flag;
4929: p_process_message := lv_process_message;
4930:
4931: IF p_event IN ( jai_constants.ar_cash_tax_confirmed , --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
4932: --Reverted from trx_type_adv_rcpts to ar_cash_tax_confirmed by Qiong for bug13730406
4933: jai_constants.trx_type_rct_rvs
4934: )
4935: THEN
4929: p_process_message := lv_process_message;
4930:
4931: IF p_event IN ( jai_constants.ar_cash_tax_confirmed , --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
4932: --Reverted from trx_type_adv_rcpts to ar_cash_tax_confirmed by Qiong for bug13730406
4933: jai_constants.trx_type_rct_rvs
4934: )
4935: THEN
4936: --Added by Qiong for bug13730406 begin
4937: ----------------------------------------------------
4934: )
4935: THEN
4936: --Added by Qiong for bug13730406 begin
4937: ----------------------------------------------------
4938: IF p_event = jai_constants.ar_cash_tax_confirmed THEN
4939: lv_document_type := jai_constants.trx_type_adv_rcpts;
4940: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 begin */
4941: ELSIF p_event = jai_constants.trx_type_rct_rvs THEN
4942: lv_document_type := jai_constants.trx_type_adv_rvs;
4935: THEN
4936: --Added by Qiong for bug13730406 begin
4937: ----------------------------------------------------
4938: IF p_event = jai_constants.ar_cash_tax_confirmed THEN
4939: lv_document_type := jai_constants.trx_type_adv_rcpts;
4940: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 begin */
4941: ELSIF p_event = jai_constants.trx_type_rct_rvs THEN
4942: lv_document_type := jai_constants.trx_type_adv_rvs;
4943: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 end */
4937: ----------------------------------------------------
4938: IF p_event = jai_constants.ar_cash_tax_confirmed THEN
4939: lv_document_type := jai_constants.trx_type_adv_rcpts;
4940: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 begin */
4941: ELSIF p_event = jai_constants.trx_type_rct_rvs THEN
4942: lv_document_type := jai_constants.trx_type_adv_rvs;
4943: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 end */
4944: ELSE
4945: ----------------------------------------------------
4938: IF p_event = jai_constants.ar_cash_tax_confirmed THEN
4939: lv_document_type := jai_constants.trx_type_adv_rcpts;
4940: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 begin */
4941: ELSIF p_event = jai_constants.trx_type_rct_rvs THEN
4942: lv_document_type := jai_constants.trx_type_adv_rvs;
4943: /*Added by Qinglei on 24-Feb-2012 for bug#13741544 end */
4944: ELSE
4945: ----------------------------------------------------
4946: --Added by Qiong for bug13730406 end
4957: p_process_flag => lv_process_flag ,
4958: p_process_message => lv_process_message
4959: );
4960:
4961: IF lv_process_flag = jai_constants.not_applicable THEN
4962: return;
4963: END IF;
4964:
4965: IF lv_process_flag = jai_constants.expected_error OR ---------C2
4961: IF lv_process_flag = jai_constants.not_applicable THEN
4962: return;
4963: END IF;
4964:
4965: IF lv_process_flag = jai_constants.expected_error OR ---------C2
4966: lv_process_flag = jai_constants.unexpected_error
4967: THEN
4968: /*
4969: || As Returned status is an error/not applicable hence:-
4962: return;
4963: END IF;
4964:
4965: IF lv_process_flag = jai_constants.expected_error OR ---------C2
4966: lv_process_flag = jai_constants.unexpected_error
4967: THEN
4968: /*
4969: || As Returned status is an error/not applicable hence:-
4970: || Set out variables p_process_flag and p_process_message accordingly
4981: p_process_message => lv_process_message
4982: );
4983:
4984:
4985: IF lv_process_flag = jai_constants.not_applicable THEN
4986: return;
4987: END IF;
4988:
4989: IF lv_process_flag = jai_constants.expected_error OR ---------C3
4985: IF lv_process_flag = jai_constants.not_applicable THEN
4986: return;
4987: END IF;
4988:
4989: IF lv_process_flag = jai_constants.expected_error OR ---------C3
4990: lv_process_flag = jai_constants.unexpected_error
4991: THEN
4992: /*
4993: || As Returned status is an error/not applicable hence:-
4986: return;
4987: END IF;
4988:
4989: IF lv_process_flag = jai_constants.expected_error OR ---------C3
4990: lv_process_flag = jai_constants.unexpected_error
4991: THEN
4992: /*
4993: || As Returned status is an error/not applicable hence:-
4994: || Set out variables p_process_flag and p_process_message accordingly
4998: return;
4999: END IF; ---------C3
5000: END IF;
5001:
5002: IF p_event = jai_constants.ar_cash THEN
5003: validate_app_unapp(p_araa => p_araa,
5004: p_document_type => lv_document_type,
5005: p_process_flag => lv_process_flag,
5006: p_process_message => lv_process_message);
5003: validate_app_unapp(p_araa => p_araa,
5004: p_document_type => lv_document_type,
5005: p_process_flag => lv_process_flag,
5006: p_process_message => lv_process_message);
5007: IF lv_process_flag = jai_constants.not_applicable THEN
5008: return;
5009: END IF;
5010:
5011: IF lv_process_flag = jai_constants.expected_error OR ---------C2
5007: IF lv_process_flag = jai_constants.not_applicable THEN
5008: return;
5009: END IF;
5010:
5011: IF lv_process_flag = jai_constants.expected_error OR ---------C2
5012: lv_process_flag = jai_constants.unexpected_error
5013: THEN
5014: /*
5015: || As Returned status is an error/not applicable hence:-
5008: return;
5009: END IF;
5010:
5011: IF lv_process_flag = jai_constants.expected_error OR ---------C2
5012: lv_process_flag = jai_constants.unexpected_error
5013: THEN
5014: /*
5015: || As Returned status is an error/not applicable hence:-
5016: || Set out variables p_process_flag and p_process_message accordingly
5030: p_process_message => lv_process_message
5031: );
5032:
5033:
5034: IF lv_process_flag = jai_constants.not_applicable THEN
5035: return;
5036: END IF;
5037:
5038: IF lv_process_flag = jai_constants.expected_error OR ---------C3
5034: IF lv_process_flag = jai_constants.not_applicable THEN
5035: return;
5036: END IF;
5037:
5038: IF lv_process_flag = jai_constants.expected_error OR ---------C3
5039: lv_process_flag = jai_constants.unexpected_error
5040: THEN
5041: /*
5042: || As Returned status is an error/not applicable hence:-
5035: return;
5036: END IF;
5037:
5038: IF lv_process_flag = jai_constants.expected_error OR ---------C3
5039: lv_process_flag = jai_constants.unexpected_error
5040: THEN
5041: /*
5042: || As Returned status is an error/not applicable hence:-
5043: || Set out variables p_process_flag and p_process_message accordingly
5097: WHERE
5098: jdt.tax_id = jtc.tax_id
5099: AND jtc.tax_type = jrttv.tax_type
5100: AND jdt.source_doc_id = p_acra.cash_receipt_id
5101: AND jrttv.regime_code = jai_constants.service_regime
5102: AND nvl(jtc.INCLUSIVE_TAX_FLAG,'N') = cp_inclusive_tax_flag
5103: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
5104:
5105: CURSOR c_get_rgm_attribute ( cp_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ,
5099: AND jtc.tax_type = jrttv.tax_type
5100: AND jdt.source_doc_id = p_acra.cash_receipt_id
5101: AND jrttv.regime_code = jai_constants.service_regime
5102: AND nvl(jtc.INCLUSIVE_TAX_FLAG,'N') = cp_inclusive_tax_flag
5103: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
5104:
5105: CURSOR c_get_rgm_attribute ( cp_regime_code JAI_RGM_DEFINITIONS.REGIME_CODE%TYPE ,
5106: cp_attribute_code JAI_RGM_REGISTRATIONS.ATTRIBUTE_CODE%TYPE ,
5107: cp_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE ,
5155: hz_cust_site_uses_all hzcsu
5156: WHERE
5157: hca.cust_account_id = hzcas.cust_account_id
5158: AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
5159: AND hzcsu.site_use_code = jai_constants.site_use_bill_to
5160: AND hca.cust_account_id = cp_party_id
5161: AND hzcsu.site_use_id = cp_party_site_id ;-- site_use_id is the party_site_id ;
5162:
5163: CURSOR cur_get_sob ( cp_org_id NUMBER )
5224: lv_member_name := 'GENERATE_DOCUMENT';
5225:
5226:
5227: lv_process_message:= null ;
5228: p_process_flag := jai_constants.successful ;
5229: p_process_message := lv_process_message ;
5230:
5231: ln_user_id := fnd_global.user_id ;
5232: ln_login_id := fnd_global.login_id ;
5234: /*################################################################################################################
5235: ||Skip the transaction if ln_rcpt_amount is 0
5236: ################################################################################################################*/
5237: IF nvl(ln_rcpt_amount,0) = 0 THEN
5238: p_process_flag := jai_constants.not_applicable ;
5239: return;
5240: END IF;
5241:
5242: OPEN cur_get_cr_details ( cp_cash_receipt_id => p_acra.cash_receipt_id );
5255:
5256: /*
5257: ||Get the value for the Batch Source Debit Memo
5258: */
5259: OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.service_regime ,
5260: cp_attribute_code => jai_constants.batch_src_dm ,
5261: cp_organization_id => ln_organization_id ,
5262: cp_location_id => ln_location_id
5263: );
5256: /*
5257: ||Get the value for the Batch Source Debit Memo
5258: */
5259: OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.service_regime ,
5260: cp_attribute_code => jai_constants.batch_src_dm ,
5261: cp_organization_id => ln_organization_id ,
5262: cp_location_id => ln_location_id
5263: );
5264: FETCH c_get_rgm_attribute INTO ln_regime_id ,lv_batch_src_dm;
5267:
5268: /*
5269: ||Get the value for the Batch Source Credit Memo
5270: */
5271: OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.service_regime ,
5272: cp_attribute_code => jai_constants.batch_src_cm ,
5273: cp_organization_id => ln_organization_id ,
5274: cp_location_id => ln_location_id
5275: );
5268: /*
5269: ||Get the value for the Batch Source Credit Memo
5270: */
5271: OPEN c_get_rgm_attribute ( cp_regime_code => jai_constants.service_regime ,
5272: cp_attribute_code => jai_constants.batch_src_cm ,
5273: cp_organization_id => ln_organization_id ,
5274: cp_location_id => ln_location_id
5275: );
5276: FETCH c_get_rgm_attribute INTO ln_regime_id ,lv_batch_src_cm;
5280: /*################################################################################################################
5281: || Derive the batch source name based on the document type
5282: ################################################################################################################*/
5283:
5284: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5285: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5286: jai_constants.trx_type_adv_rcpts ,
5287: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5288: )
5282: ################################################################################################################*/
5283:
5284: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5285: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5286: jai_constants.trx_type_adv_rcpts ,
5287: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5288: )
5289: THEN
5290: lv_batch_src_name := lv_batch_src_dm; /* Service Debit Memo */
5283:
5284: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5285: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5286: jai_constants.trx_type_adv_rcpts ,
5287: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5288: )
5289: THEN
5290: lv_batch_src_name := lv_batch_src_dm; /* Service Debit Memo */
5291: lv_trx_number_prefix := 'Service-DM';
5288: )
5289: THEN
5290: lv_batch_src_name := lv_batch_src_dm; /* Service Debit Memo */
5291: lv_trx_number_prefix := 'Service-DM';
5292: ELSIF p_document_type IN ( jai_constants.trx_type_adv_app,
5293: jai_constants.trx_type_adv_rvs--Modified by Qinglei on 24-Dec-2012 for bug#13741544
5294: )
5295:
5296: THEN
5289: THEN
5290: lv_batch_src_name := lv_batch_src_dm; /* Service Debit Memo */
5291: lv_trx_number_prefix := 'Service-DM';
5292: ELSIF p_document_type IN ( jai_constants.trx_type_adv_app,
5293: jai_constants.trx_type_adv_rvs--Modified by Qinglei on 24-Dec-2012 for bug#13741544
5294: )
5295:
5296: THEN
5297: lv_batch_src_name := lv_batch_src_cm; /* Service Credit Memo */
5299: ELSE
5300: /*
5301: ||Skip the transaction
5302: */
5303: p_process_flag := jai_constants.not_applicable;
5304: p_process_message := null;
5305: return ;
5306: END IF;
5307:
5312: /*
5313: || Error out if the batch source name is null i.e regime party setup for
5314: */
5315: IF lv_batch_src_name IS NULL THEN
5316: p_process_flag := jai_constants.expected_error;
5317: lv_process_message := 'Batch source definition has not be defined for the inventory organization '||ln_organization_id;
5318: p_process_message := lv_process_message ;
5319: return;
5320: END IF;
5326: FETCH cur_get_batch_source INTO rec_cur_get_batch_source;
5327: IF cur_get_batch_source%NOTFOUND THEN
5328:
5329: CLOSE cur_get_batch_source ;
5330: p_process_flag := jai_constants.expected_error;
5331: lv_process_message := 'Service batch source has not been defined for '||lv_batch_src_name ||'. Cannot process transaction ';
5332: p_process_message := lv_process_message ;
5333: return;
5334: END IF;
5338: /*################################################################################################################
5339: || DERIVE THE TERM FOR DM'S ONLY
5340: ################################################################################################################*/
5341:
5342: IF rec_cur_get_batch_source.type = jai_constants.ar_doc_type_dm THEN
5343: ln_term_id := rec_cur_get_batch_source.default_term;
5344: /*
5345: || Throw an error if the term has not been defined for the debit memo Transaction type .
5346: || This check is not required in case of credit memo
5345: || Throw an error if the term has not been defined for the debit memo Transaction type .
5346: || This check is not required in case of credit memo
5347: */
5348: IF ln_term_id IS NULL THEN
5349: p_process_flag := jai_constants.expected_error;
5350: lv_process_message := 'Cannot process transaction. A default term needs to be defined FOR the Transaction TYPE '||rec_cur_get_batch_source.name ;
5351: p_process_message := lv_process_message ;
5352: return;
5353: END IF;
5363: /*
5364: ||Throw an error if the Set of books has not been defined
5365: */
5366: IF lv_set_of_books_id IS NULL THEN
5367: p_process_flag := jai_constants.expected_error;
5368: lv_process_message := 'Set of books not defined for the org id.';
5369: p_process_message := lv_process_message ;
5370: return;
5371: END IF;
5384: ||Throw an error if the bill to address has not been defined
5385: */
5386:
5387: IF ln_bill_to_address_id IS NULL THEN
5388: p_process_flag := jai_constants.expected_error;
5389: lv_process_message := 'Bill to address not defined for the customer id -> '||rec_cur_get_cr_details.customer_id ||' customer site -> '||p_acra.CUSTOMER_SITE_USE_ID;
5390: p_process_message := lv_process_message ;
5391: return;
5392: END IF;
5401: || Generate DM/CM for exclusive taxes
5402: ################################################################################################################*/
5403: FOR rec_cur_get_rcpt_taxes IN cur_get_rcpt_taxes(cp_inclusive_tax_flag => 'N')
5404: LOOP
5405: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5406: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5407: jai_constants.trx_type_adv_rcpts ,
5408: jai_constants.trx_type_adv_rvs /* Receipt reversal */
5409: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5403: FOR rec_cur_get_rcpt_taxes IN cur_get_rcpt_taxes(cp_inclusive_tax_flag => 'N')
5404: LOOP
5405: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5406: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5407: jai_constants.trx_type_adv_rcpts ,
5408: jai_constants.trx_type_adv_rvs /* Receipt reversal */
5409: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5410: ) THEN
5411: ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
5404: LOOP
5405: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5406: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5407: jai_constants.trx_type_adv_rcpts ,
5408: jai_constants.trx_type_adv_rvs /* Receipt reversal */
5409: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5410: ) THEN
5411: ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
5412: --ld_trx_date := rec_cur_get_cr_details.gl_date;
5413: /*Commented by Qinglei on 12-Jan-2012 for bug#13557031*/
5414: lv_trx_number := lv_trx_number_prefix||p_acra.CASH_RECEIPT_ID ;
5415:
5416: /*Added by Qinglei on 12-Jan-2012 for bug#13557031 begin*/
5417: --IF p_document_type = jai_constants.ar_cash_tax_confirmed THEN
5418: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5419: IF p_document_type = jai_constants.trx_type_adv_rcpts THEN
5420: ld_trx_date := p_acra.receipt_date;
5421: ELSIF p_document_type = jai_constants.trx_type_adv_rvs THEN
5415:
5416: /*Added by Qinglei on 12-Jan-2012 for bug#13557031 begin*/
5417: --IF p_document_type = jai_constants.ar_cash_tax_confirmed THEN
5418: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5419: IF p_document_type = jai_constants.trx_type_adv_rcpts THEN
5420: ld_trx_date := p_acra.receipt_date;
5421: ELSIF p_document_type = jai_constants.trx_type_adv_rvs THEN
5422: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5423: ld_trx_date := p_acra.reversal_date;
5417: --IF p_document_type = jai_constants.ar_cash_tax_confirmed THEN
5418: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5419: IF p_document_type = jai_constants.trx_type_adv_rcpts THEN
5420: ld_trx_date := p_acra.receipt_date;
5421: ELSIF p_document_type = jai_constants.trx_type_adv_rvs THEN
5422: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5423: ld_trx_date := p_acra.reversal_date;
5424: END IF;
5425: /*Added by Qinglei on 12-Jan-2012 for bug#13557031 end*/
5422: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5423: ld_trx_date := p_acra.reversal_date;
5424: END IF;
5425: /*Added by Qinglei on 12-Jan-2012 for bug#13557031 end*/
5426: ELSIF p_document_type IN ( jai_constants.trx_type_adv_app , /* Receipt application */
5427: jai_constants.trx_type_adv_unapp /* Receipt unapplication */
5428: ) THEN
5429: /*
5430: For receipt application and un-application event, the amount generated for gl entry is determined by
5423: ld_trx_date := p_acra.reversal_date;
5424: END IF;
5425: /*Added by Qinglei on 12-Jan-2012 for bug#13557031 end*/
5426: ELSIF p_document_type IN ( jai_constants.trx_type_adv_app , /* Receipt application */
5427: jai_constants.trx_type_adv_unapp /* Receipt unapplication */
5428: ) THEN
5429: /*
5430: For receipt application and un-application event, the amount generated for gl entry is determined by
5431: the allocated receipt amount.
5432: */
5433: lv_trx_number := lv_trx_number_prefix||p_araa.RECEIVABLE_APPLICATION_ID;
5434: ln_rcpt_tax_amount := round(rec_cur_get_rcpt_taxes.func_tax_amt *
5435: (nvl(p_araa.ACCTD_AMOUNT_APPLIED_FROM,0)/
5436: (p_acra.AMOUNT * NVL(ln_converted_rate,1))),jai_constants.service_rgm_rnd_factor);
5437: /*Added by Qinglei on 12-Jan-2012 for bug#13557031*/
5438: ld_trx_date := p_araa.apply_date;
5439:
5440: END IF;
5451: || 1.+ve if the creation sign of the document is positive
5452: || 1.-ve if the creation sign of the document is -ve
5453: || If the sign is any sign then for a DM create a +ve amount and CM would ve created with a -ve amount
5454: */
5455: IF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_positive THEN
5456: ln_amount := abs(ln_rcpt_tax_amount) * 1;
5457:
5458: ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_negative THEN
5459: ln_amount := abs(ln_rcpt_tax_amount) * -1;
5454: */
5455: IF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_positive THEN
5456: ln_amount := abs(ln_rcpt_tax_amount) * 1;
5457:
5458: ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_negative THEN
5459: ln_amount := abs(ln_rcpt_tax_amount) * -1;
5460:
5461: ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_any THEN
5462:
5457:
5458: ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_negative THEN
5459: ln_amount := abs(ln_rcpt_tax_amount) * -1;
5460:
5461: ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_any THEN
5462:
5463: IF rec_cur_get_batch_source.type = jai_constants.ar_doc_type_dm THEN
5464: ln_amount := abs(ln_rcpt_tax_amount) ;
5465:
5459: ln_amount := abs(ln_rcpt_tax_amount) * -1;
5460:
5461: ELSIF rec_cur_get_batch_source.creation_sign = jai_constants.creation_sign_any THEN
5462:
5463: IF rec_cur_get_batch_source.type = jai_constants.ar_doc_type_dm THEN
5464: ln_amount := abs(ln_rcpt_tax_amount) ;
5465:
5466: ELSIF rec_cur_get_batch_source.type = jai_constants.ar_invoice_type_cm THEN
5467: ln_amount := abs(ln_rcpt_tax_amount) * -1;
5462:
5463: IF rec_cur_get_batch_source.type = jai_constants.ar_doc_type_dm THEN
5464: ln_amount := abs(ln_rcpt_tax_amount) ;
5465:
5466: ELSIF rec_cur_get_batch_source.type = jai_constants.ar_invoice_type_cm THEN
5467: ln_amount := abs(ln_rcpt_tax_amount) * -1;
5468: END IF;
5469: END IF;
5470:
5481: rec_ra_interface_lines.trx_date := ld_trx_date;
5482: rec_ra_interface_lines.trx_number := lv_trx_number;
5483: rec_ra_interface_lines.batch_source_name := lv_batch_src_name;
5484: rec_ra_interface_lines.cust_trx_type_name := rec_cur_get_batch_source.name;
5485: rec_ra_interface_lines.line_type := jai_constants.line_type_line;
5486: rec_ra_interface_lines.conversion_rate := 1;
5487: rec_ra_interface_lines.conversion_type := jai_constants.conversion_type_user;
5488: rec_ra_interface_lines.interface_line_context := lv_batch_src_name;
5489: rec_ra_interface_lines.interface_line_attribute2 := ln_transaction_id;
5483: rec_ra_interface_lines.batch_source_name := lv_batch_src_name;
5484: rec_ra_interface_lines.cust_trx_type_name := rec_cur_get_batch_source.name;
5485: rec_ra_interface_lines.line_type := jai_constants.line_type_line;
5486: rec_ra_interface_lines.conversion_rate := 1;
5487: rec_ra_interface_lines.conversion_type := jai_constants.conversion_type_user;
5488: rec_ra_interface_lines.interface_line_context := lv_batch_src_name;
5489: rec_ra_interface_lines.interface_line_attribute2 := ln_transaction_id;
5490: rec_ra_interface_lines.currency_code := jai_constants.func_curr;
5491: rec_ra_interface_lines.primary_salesrep_id := -3;
5486: rec_ra_interface_lines.conversion_rate := 1;
5487: rec_ra_interface_lines.conversion_type := jai_constants.conversion_type_user;
5488: rec_ra_interface_lines.interface_line_context := lv_batch_src_name;
5489: rec_ra_interface_lines.interface_line_attribute2 := ln_transaction_id;
5490: rec_ra_interface_lines.currency_code := jai_constants.func_curr;
5491: rec_ra_interface_lines.primary_salesrep_id := -3;
5492: rec_ra_interface_lines.tax_code := NULL;
5493: rec_ra_interface_lines.term_id := ln_term_id;
5494: rec_ra_interface_lines.warehouse_id := ln_organization_id;
5504:
5505: rec_ra_interface_dist.interface_line_id := ln_transaction_id;
5506: rec_ra_interface_dist.interface_line_context := lv_batch_src_name;
5507: rec_ra_interface_dist.interface_line_attribute2 := ln_transaction_id;
5508: rec_ra_interface_dist.account_class := jai_constants.account_class_rev;
5509: rec_ra_interface_dist.amount := ln_amount;
5510: rec_ra_interface_dist.acctd_amount := ln_amount;
5511: rec_ra_interface_dist.created_by := ln_user_id;
5512: rec_ra_interface_dist.creation_date := SYSDATE;
5530: || Insert GL interface for inclusive taxes
5531: ################################################################################################################*/
5532: FOR rec_cur_get_rcpt_taxes IN cur_get_rcpt_taxes(cp_inclusive_tax_flag => 'Y')
5533: LOOP
5534: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5535: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5536: jai_constants.trx_type_adv_rcpts,
5537: jai_constants.trx_type_adv_rvs /* Receipt reversal */
5538: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5532: FOR rec_cur_get_rcpt_taxes IN cur_get_rcpt_taxes(cp_inclusive_tax_flag => 'Y')
5533: LOOP
5534: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5535: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5536: jai_constants.trx_type_adv_rcpts,
5537: jai_constants.trx_type_adv_rvs /* Receipt reversal */
5538: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5539: ) THEN
5540: ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
5533: LOOP
5534: IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
5535: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5536: jai_constants.trx_type_adv_rcpts,
5537: jai_constants.trx_type_adv_rvs /* Receipt reversal */
5538: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5539: ) THEN
5540: ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
5541: ln_rcpt_tran_tax_amount := rec_cur_get_rcpt_taxes.tax_amount;
5539: ) THEN
5540: ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
5541: ln_rcpt_tran_tax_amount := rec_cur_get_rcpt_taxes.tax_amount;
5542:
5543: ELSIF p_document_type IN ( jai_constants.trx_type_adv_app , /* Receipt application */
5544: jai_constants.trx_type_adv_unapp /* Receipt unapplication */
5545: ) THEN
5546: /*
5547: For receipt application and un-application event, the amount generated for gl entry is determined by
5540: ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
5541: ln_rcpt_tran_tax_amount := rec_cur_get_rcpt_taxes.tax_amount;
5542:
5543: ELSIF p_document_type IN ( jai_constants.trx_type_adv_app , /* Receipt application */
5544: jai_constants.trx_type_adv_unapp /* Receipt unapplication */
5545: ) THEN
5546: /*
5547: For receipt application and un-application event, the amount generated for gl entry is determined by
5548: the allocated receipt amount.
5569: END LOOP;
5570:
5571: EXCEPTION
5572: WHEN OTHERS THEN
5573: p_process_flag := jai_constants.unexpected_error;
5574: p_process_message := ' Unexpected error occured while processing jai_ar_rgm_processing_pkg.generate_documents'||substr(SQLERRM,1,1500) ;
5575: END generate_documents;
5576:
5577: --========================================================================
5606: lv_process_message VARCHAR2(4000);
5607:
5608: BEGIN
5609:
5610: lv_process_flag := jai_constants.successful;
5611: lv_process_message := null;
5612:
5613: p_process_flag := lv_process_flag;
5614: p_process_message := lv_process_message;
5686: *********************************************************************************************************/
5687:
5688: ln_ccid_tax_type := jai_cmn_rgm_recording_pkg.get_account (
5689: p_regime_id => pn_regime_id,
5690: p_organization_type => jai_constants.orgn_type_io,
5691: p_organization_id => pn_organization_id,
5692: p_location_id => pn_location_id,
5693: p_tax_type => pv_tax_type,
5694: p_account_name => jai_constants.liability
5690: p_organization_type => jai_constants.orgn_type_io,
5691: p_organization_id => pn_organization_id,
5692: p_location_id => pn_location_id,
5693: p_tax_type => pv_tax_type,
5694: p_account_name => jai_constants.liability
5695: );
5696: IF ln_ccid_tax_type IS NULL THEN
5697: /**********************************************************************************************************
5698: || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
5698: || Code Combination id has been returned as null from the function jai_cmn_rgm_recording_pkg.get_account
5699: || This is an error condition and the current processing has to be stopped
5700: **********************************************************************************************************/
5701:
5702: p_process_flag := jai_constants.expected_error;
5703: lv_process_message := 'Invalid Code combination ,please check the Service Tax - Tax Accounting Setup';
5704: p_process_message := lv_process_message ;
5705: rollback;
5706: return;
5818: lv_attribute1 VARCHAR2(240);
5819: lv_attribute2 VARCHAR2(240);
5820:
5821: BEGIN
5822: lv_process_flag := jai_constants.successful;
5823: lv_process_message := null;
5824:
5825: p_process_flag := lv_process_flag;
5826: p_process_message := lv_process_message;
5824:
5825: p_process_flag := lv_process_flag;
5826: p_process_message := lv_process_message;
5827: lv_tax_type := pv_tax_type;
5828: ln_entered_amt := round(abs(pn_tran_tax_amount), jai_constants.service_rgm_rnd_factor);
5829: ln_accounted_amt := round(abs(pn_func_tax_amount), jai_constants.service_rgm_rnd_factor);
5830:
5831: ln_tax_type_ccid := jai_cmn_rgm_recording_pkg.get_account(
5832: p_regime_id => pn_regime_id,
5825: p_process_flag := lv_process_flag;
5826: p_process_message := lv_process_message;
5827: lv_tax_type := pv_tax_type;
5828: ln_entered_amt := round(abs(pn_tran_tax_amount), jai_constants.service_rgm_rnd_factor);
5829: ln_accounted_amt := round(abs(pn_func_tax_amount), jai_constants.service_rgm_rnd_factor);
5830:
5831: ln_tax_type_ccid := jai_cmn_rgm_recording_pkg.get_account(
5832: p_regime_id => pn_regime_id,
5833: p_organization_type => jai_constants.orgn_type_io,
5829: ln_accounted_amt := round(abs(pn_func_tax_amount), jai_constants.service_rgm_rnd_factor);
5830:
5831: ln_tax_type_ccid := jai_cmn_rgm_recording_pkg.get_account(
5832: p_regime_id => pn_regime_id,
5833: p_organization_type => jai_constants.orgn_type_io,
5834: p_organization_id => pn_organization_id,
5835: p_location_id => pn_location_id,
5836: p_tax_type => lv_tax_type,
5837: p_account_name => jai_constants.liability);
5833: p_organization_type => jai_constants.orgn_type_io,
5834: p_organization_id => pn_organization_id,
5835: p_location_id => pn_location_id,
5836: p_tax_type => lv_tax_type,
5837: p_account_name => jai_constants.liability);
5838: IF ln_tax_type_ccid IS NULL THEN
5839: lv_process_message := 'Liability Account is not defined at the regime registration.';
5840: RAISE_APPLICATION_ERROR(-20007, 'Error - package jai_ar_rgm_processing_pkg.generate_gl_entry: '
5841: ||lv_process_message);
5849: ld_curr_conv_date := p_jacr.EXCHANGE_DATE;
5850: lv_curr_conv_type := p_jacr.EXCHANGE_RATE_TYPE;
5851: ln_curr_conv_rate := p_jacr.EXCHANGE_RATE;
5852:
5853: IF p_document_type IN (--jai_constants.ar_cash_tax_confirmed, /* Receipt confirmation */
5854: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5855: jai_constants.trx_type_adv_rcpts,
5856: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5857: )
5851: ln_curr_conv_rate := p_jacr.EXCHANGE_RATE;
5852:
5853: IF p_document_type IN (--jai_constants.ar_cash_tax_confirmed, /* Receipt confirmation */
5854: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5855: jai_constants.trx_type_adv_rcpts,
5856: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5857: )
5858: THEN
5859: IF p_document_type IN ( jai_constants.trx_type_adv_rcpts)
5852:
5853: IF p_document_type IN (--jai_constants.ar_cash_tax_confirmed, /* Receipt confirmation */
5854: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5855: jai_constants.trx_type_adv_rcpts,
5856: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5857: )
5858: THEN
5859: IF p_document_type IN ( jai_constants.trx_type_adv_rcpts)
5860: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5855: jai_constants.trx_type_adv_rcpts,
5856: jai_constants.trx_type_adv_unapp /* Receipt unapplication*/
5857: )
5858: THEN
5859: IF p_document_type IN ( jai_constants.trx_type_adv_rcpts)
5860: --Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
5861: THEN
5862: lv_repository_name := 'cash_receipt_id';
5863: lv_call_from := 'CONFIRM_ACCOUNTING';
5864: lv_source_table_name := 'AR_CASH_RECEIPTS_ALL';
5865: ln_source_document_id := p_jacr.cash_receipt_id;
5866: ld_transaction_date := p_jacr.CONFIRM_DATE;
5867: ld_gl_date := p_jacr.gl_date;
5868: lv_reference_name := jai_constants.jai_cash_rcpts;
5869: ln_reference_id := p_jacr.cash_receipt_id;
5870: lv_attribute1 := p_acra.RECEIPT_NUMBER;
5871: ELSE
5872: ln_accounted_amt := ln_accounted_amt;
5883: END IF;
5884: ln_dr_account := ln_receipt_unapplied_account;
5885: ln_cr_account := ln_tax_type_ccid;
5886:
5887: ELSIF p_document_type IN (jai_constants.trx_type_adv_app,
5888: jai_constants.trx_type_adv_rvs
5889: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5890: )
5891: THEN
5884: ln_dr_account := ln_receipt_unapplied_account;
5885: ln_cr_account := ln_tax_type_ccid;
5886:
5887: ELSIF p_document_type IN (jai_constants.trx_type_adv_app,
5888: jai_constants.trx_type_adv_rvs
5889: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5890: )
5891: THEN
5892: IF p_document_type IN ( jai_constants.trx_type_adv_rvs)
5888: jai_constants.trx_type_adv_rvs
5889: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5890: )
5891: THEN
5892: IF p_document_type IN ( jai_constants.trx_type_adv_rvs)
5893: --Modified by Qinglei on 24-Feb-2012 for bug#13741544
5894: THEN
5895: /*Added by Qinglei on 12-Jan-2012 for bug#13569242 begin*/
5896: OPEN cur_get_rcpt_reversal_gl_date;
5903: ln_source_document_id := p_jacr.cash_receipt_id;
5904: ld_transaction_date := p_acra.reversal_date;
5905: --ld_gl_date := p_acra.reversal_date;
5906: /*Commented by Qinglei on 12-Jan-2012 for bug#13569242*/
5907: lv_reference_name := jai_constants.jai_cash_rcpts;
5908: ln_reference_id := p_jacr.cash_receipt_id;
5909: lv_attribute1 := p_acra.RECEIPT_NUMBER;
5910: ELSE
5911: lv_repository_name := 'receivable_application_id';
5926: END IF;
5927:
5928: IF nvl(ln_entered_amt, 0) <> 0 THEN
5929: jai_cmn_rgm_recording_pkg.post_accounting(
5930: p_regime_code => jai_constants.service_regime,
5931: p_tax_type => lv_tax_type,
5932: p_organization_type => jai_constants.service_tax_orgn_type,
5933: p_organization_id => pn_organization_id,
5934: p_source => jai_constants.source_ar,
5928: IF nvl(ln_entered_amt, 0) <> 0 THEN
5929: jai_cmn_rgm_recording_pkg.post_accounting(
5930: p_regime_code => jai_constants.service_regime,
5931: p_tax_type => lv_tax_type,
5932: p_organization_type => jai_constants.service_tax_orgn_type,
5933: p_organization_id => pn_organization_id,
5934: p_source => jai_constants.source_ar,
5935: p_source_trx_type => p_document_type,
5936: p_source_table_name => lv_source_table_name,
5930: p_regime_code => jai_constants.service_regime,
5931: p_tax_type => lv_tax_type,
5932: p_organization_type => jai_constants.service_tax_orgn_type,
5933: p_organization_id => pn_organization_id,
5934: p_source => jai_constants.source_ar,
5935: p_source_trx_type => p_document_type,
5936: p_source_table_name => lv_source_table_name,
5937: p_source_document_id => ln_source_document_id,
5938: p_code_combination_id => ln_cr_account,
5955: p_attribute2 => lv_attribute2
5956: );
5957:
5958: jai_cmn_rgm_recording_pkg.post_accounting(
5959: p_regime_code => jai_constants.service_regime,
5960: p_tax_type => lv_tax_type,
5961: p_organization_type => jai_constants.service_tax_orgn_type,
5962: p_organization_id => pn_organization_id,
5963: p_source => jai_constants.source_ar,
5957:
5958: jai_cmn_rgm_recording_pkg.post_accounting(
5959: p_regime_code => jai_constants.service_regime,
5960: p_tax_type => lv_tax_type,
5961: p_organization_type => jai_constants.service_tax_orgn_type,
5962: p_organization_id => pn_organization_id,
5963: p_source => jai_constants.source_ar,
5964: p_source_trx_type => p_document_type,
5965: p_source_table_name => lv_source_table_name,
5959: p_regime_code => jai_constants.service_regime,
5960: p_tax_type => lv_tax_type,
5961: p_organization_type => jai_constants.service_tax_orgn_type,
5962: p_organization_id => pn_organization_id,
5963: p_source => jai_constants.source_ar,
5964: p_source_trx_type => p_document_type,
5965: p_source_table_name => lv_source_table_name,
5966: p_source_document_id => ln_source_document_id,
5967: p_code_combination_id => ln_dr_account,
6019: jai_regime_tax_types_v jrttv
6020: WHERE jcra.cash_receipt_id = cp_cash_receipt_id
6021: AND jcra.cash_receipt_id = jdt.source_doc_id
6022: AND jdt.tax_type = jrttv.tax_type
6023: AND jrttv.regime_code = jai_constants.service_regime
6024: AND jcra.confirm_flag = jai_constants.yes
6025: AND jdt.source_doc_type = jai_constants.ar_cash;
6026:
6027: BEGIN
6020: WHERE jcra.cash_receipt_id = cp_cash_receipt_id
6021: AND jcra.cash_receipt_id = jdt.source_doc_id
6022: AND jdt.tax_type = jrttv.tax_type
6023: AND jrttv.regime_code = jai_constants.service_regime
6024: AND jcra.confirm_flag = jai_constants.yes
6025: AND jdt.source_doc_type = jai_constants.ar_cash;
6026:
6027: BEGIN
6028: lv_process_flag := jai_constants.successful;
6021: AND jcra.cash_receipt_id = jdt.source_doc_id
6022: AND jdt.tax_type = jrttv.tax_type
6023: AND jrttv.regime_code = jai_constants.service_regime
6024: AND jcra.confirm_flag = jai_constants.yes
6025: AND jdt.source_doc_type = jai_constants.ar_cash;
6026:
6027: BEGIN
6028: lv_process_flag := jai_constants.successful;
6029: lv_process_message := NULL;
6024: AND jcra.confirm_flag = jai_constants.yes
6025: AND jdt.source_doc_type = jai_constants.ar_cash;
6026:
6027: BEGIN
6028: lv_process_flag := jai_constants.successful;
6029: lv_process_message := NULL;
6030:
6031: p_process_flag := lv_process_flag;
6032: p_process_message := lv_process_message;
6035: FETCH cur_chk_st_applicable INTO ln_exists;
6036: CLOSE cur_chk_st_applicable;
6037:
6038: IF nvl(ln_exists,-1) <> 1 THEN
6039: p_process_flag := jai_constants.not_applicable;
6040: RETURN;
6041: END IF;
6042:
6043: END validate_receipts;
6094: WHERE
6095: jcra.cash_receipt_id = cp_cash_receipt_id
6096: AND jcra.cash_receipt_id = jdt.source_doc_id
6097: AND jdt.tax_type = jrttv.tax_type
6098: AND jrttv.regime_code = jai_constants.service_regime
6099: AND jcra.confirm_flag = jai_constants.yes
6100: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
6101: BEGIN
6102: ln_sign_of_app := sign(nvl(p_araa.AMOUNT_APPLIED,0));
6095: jcra.cash_receipt_id = cp_cash_receipt_id
6096: AND jcra.cash_receipt_id = jdt.source_doc_id
6097: AND jdt.tax_type = jrttv.tax_type
6098: AND jrttv.regime_code = jai_constants.service_regime
6099: AND jcra.confirm_flag = jai_constants.yes
6100: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
6101: BEGIN
6102: ln_sign_of_app := sign(nvl(p_araa.AMOUNT_APPLIED,0));
6103:
6096: AND jcra.cash_receipt_id = jdt.source_doc_id
6097: AND jdt.tax_type = jrttv.tax_type
6098: AND jrttv.regime_code = jai_constants.service_regime
6099: AND jcra.confirm_flag = jai_constants.yes
6100: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
6101: BEGIN
6102: ln_sign_of_app := sign(nvl(p_araa.AMOUNT_APPLIED,0));
6103:
6104: IF p_araa.APPLICATION_TYPE IN (jai_constants.ar_cash,
6100: AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
6101: BEGIN
6102: ln_sign_of_app := sign(nvl(p_araa.AMOUNT_APPLIED,0));
6103:
6104: IF p_araa.APPLICATION_TYPE IN (jai_constants.ar_cash,
6105: jai_constants.ar_status_activity)
6106: AND p_araa.cash_receipt_id IS NOT NULL THEN
6107:
6108: OPEN cur_get_cr_sign(p_araa.cash_receipt_id);
6101: BEGIN
6102: ln_sign_of_app := sign(nvl(p_araa.AMOUNT_APPLIED,0));
6103:
6104: IF p_araa.APPLICATION_TYPE IN (jai_constants.ar_cash,
6105: jai_constants.ar_status_activity)
6106: AND p_araa.cash_receipt_id IS NOT NULL THEN
6107:
6108: OPEN cur_get_cr_sign(p_araa.cash_receipt_id);
6109: FETCH cur_get_cr_sign INTO ln_sign_of_app_fr_doc;
6108: OPEN cur_get_cr_sign(p_araa.cash_receipt_id);
6109: FETCH cur_get_cr_sign INTO ln_sign_of_app_fr_doc;
6110: CLOSE cur_get_cr_sign;
6111: IF ln_sign_of_app = ln_sign_of_app_fr_doc THEN
6112: lv_app_doc_type := jai_constants.trx_type_adv_app;
6113: ELSE
6114: lv_app_doc_type := jai_constants.trx_type_adv_unapp;
6115: END IF;
6116:
6110: CLOSE cur_get_cr_sign;
6111: IF ln_sign_of_app = ln_sign_of_app_fr_doc THEN
6112: lv_app_doc_type := jai_constants.trx_type_adv_app;
6113: ELSE
6114: lv_app_doc_type := jai_constants.trx_type_adv_unapp;
6115: END IF;
6116:
6117: OPEN cur_chk_st_applicable(p_araa.cash_receipt_id);
6118: FETCH cur_chk_st_applicable INTO ln_exists;
6117: OPEN cur_chk_st_applicable(p_araa.cash_receipt_id);
6118: FETCH cur_chk_st_applicable INTO ln_exists;
6119: CLOSE cur_chk_st_applicable;
6120: IF nvl(ln_exists,0) <> 1 THEN
6121: p_process_flag := jai_constants.not_applicable;
6122: RETURN;
6123: END IF;
6124: END IF;
6125: