[Home] [Help]
3057: || and the same value retained for this record set of same cust / cust site
3058: || a.2) If the parameter p_single_invoice_num is set to 'N' , then for each delivery a seperate vat invoice number
3059: || will be generated.
3060: || If the generate vat invoice number api returns error , then the subsection b will not be processed and the delivery
3061: || will be flagged as errored in the table JAI_RGM_INVOICE_GEN_T for the delivery_id
3062: ||
3063: || b) make a call to the api to process accounting
3064: ||
3065: || b.1 If it returns success then if the p_process_action = 'All' then flag both the fields VAT_INV_GEN_STATUS
3259: NVL(wdd.inv_interfaced_flag,'N') <> 'Y';
3260:
3261: /*Bug 6031031 - Start*/
3262: -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3263: CURSOR c_vat_inv_gen_status (cp_delivery_id jai_rgm_invoice_gen_t.delivery_id%type
3264: ,cp_order_line_id jai_rgm_invoice_gen_t.order_line_id%type)
3265: IS
3266: SELECT vat_inv_gen_status
3267: FROM JAI_RGM_INVOICE_GEN_T
3260:
3261: /*Bug 6031031 - Start*/
3262: -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3263: CURSOR c_vat_inv_gen_status (cp_delivery_id jai_rgm_invoice_gen_t.delivery_id%type
3264: ,cp_order_line_id jai_rgm_invoice_gen_t.order_line_id%type)
3265: IS
3266: SELECT vat_inv_gen_status
3267: FROM JAI_RGM_INVOICE_GEN_T
3268: --WHERE delivery_id = cp_delivery_id ;
3263: CURSOR c_vat_inv_gen_status (cp_delivery_id jai_rgm_invoice_gen_t.delivery_id%type
3264: ,cp_order_line_id jai_rgm_invoice_gen_t.order_line_id%type)
3265: IS
3266: SELECT vat_inv_gen_status
3267: FROM JAI_RGM_INVOICE_GEN_T
3268: --WHERE delivery_id = cp_delivery_id ;
3269: WHERE delivery_id = NVL(cp_delivery_id, -1)
3270: OR order_line_id = NVL(cp_order_line_id, -1);
3271: -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
3272: lv_vat_inv_gen_status VARCHAR2(1);
3273: /*Bug 6031031 - End*/
3274:
3275: -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
3276: CURSOR c_get_excise_from_shippable (cp_order_number jai_rgm_invoice_gen_t.order_number%TYPE)
3277: IS
3278: SELECT jowla.excise_invoice_no
3279: ,jowla.excise_invoice_date
3280: FROM JAI_RGM_INVOICE_GEN_T jrigt
3276: CURSOR c_get_excise_from_shippable (cp_order_number jai_rgm_invoice_gen_t.order_number%TYPE)
3277: IS
3278: SELECT jowla.excise_invoice_no
3279: ,jowla.excise_invoice_date
3280: FROM JAI_RGM_INVOICE_GEN_T jrigt
3281: , JAI_OM_WSH_LINES_ALL jowla
3282: WHERE jrigt.program_id = ln_conc_progam_id
3283: AND jrigt.delivery_id = jowla.delivery_id
3284: AND jowla.excise_invoice_no IS NOT NULL
3369: sql_stmt_shippable
3370: := 'SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no, '||
3371: 'party_id , party_site_id , party_type , vat_inv_gen_status , vat_acct_status, '||
3372: 'order_line_id, order_number ' ||
3373: 'FROM JAI_RGM_INVOICE_GEN_T jrigt ' ||
3374: 'WHERE regime_id = '||p_regime_id ||' '||
3375: 'AND registration_num = NVL('||lv_p_registration_num_str||',registration_num) '||
3376: 'AND (delivery_id BETWEEN NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
3377: 'NVL('||NVL(TO_CHAR(p_delivery_id_to), 'NULL')||',delivery_id)) '||
3399: sql_stmt_all
3400: := 'SELECT delivery_id , delivery_date , organization_id , location_id , vat_invoice_no, '||
3401: 'party_id , party_site_id , party_type , vat_inv_gen_status , vat_acct_status, '||
3402: 'order_line_id, order_number ' ||
3403: 'FROM JAI_RGM_INVOICE_GEN_T jrigt ' ||
3404: 'WHERE regime_id = '||p_regime_id ||' '||
3405: 'AND registration_num = NVL('||lv_p_registration_num_str||',registration_num) '||
3406: 'AND (delivery_id IS NULL OR (delivery_id BETWEEN '||
3407: 'NVL('||NVL(TO_CHAR(p_delivery_id_from), 'NULL')||',delivery_id) AND '||
3557: IF NVL(lv_Same_invoice_no,jai_constants.no) = jai_constants.yes --same as excise
3558: THEN
3559: /* detailed logic of using excise invoice number as VAT invoice number
3560: 1. if excise is not generated, then raise error msg and go NEXTDELIVERY;
3561: 2. else, use excise as VAT invoice number, and update JAI_RGM_INVOICE_GEN_T
3562: (vat_invoice_no => excise_inv_number, vat_inv_gen_status => 'C');
3563: */
3564: IF lv_debug = 'Y' THEN
3565: Fnd_File.PUT_LINE(Fnd_File.LOG, ' lv_Same_invoice_no = Y , Hence we need not call inv num generation routine - using excise inv num instead');
3618: , vat_invoice_date = excise_invoice_date
3619: WHERE delivery_id = mainrec.delivery_id;
3620:
3621: IF lv_debug = 'Y' THEN
3622: Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
3623: END IF;
3624:
3625: UPDATE JAI_RGM_INVOICE_GEN_T
3626: SET vat_invoice_no = lv_vat_invoice_number
3621: IF lv_debug = 'Y' THEN
3622: Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
3623: END IF;
3624:
3625: UPDATE JAI_RGM_INVOICE_GEN_T
3626: SET vat_invoice_no = lv_vat_invoice_number
3627: , vat_inv_gen_status = 'C'
3628: , vat_inv_gen_err_message = NULL
3629: , request_id = ln_conc_request_id
3633: , last_update_Date = sysdate
3634: WHERE Delivery_id = mainrec.delivery_id;
3635:
3636: IF lv_debug = 'Y' THEN
3637: Fnd_File.PUT_LINE(Fnd_File.LOG, ' after updating jai_rgm_invoice_gen_t for ex inv num');
3638: END IF;
3639:
3640: ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
3641:
3669: THEN
3670: /* generate new VAT invoice number by document sequence;
3671: 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
3672: 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
3673: table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number
3674: , vat_inv_gen_status => 'C');
3675: */
3676: IF lv_Debug = 'Y'
3677: THEN
3713: , LAST_UPDATE_LOGIN = fnd_global.login_id
3714: , LAST_UPDATED_BY = fnd_global.user_id
3715: WHERE DELIVERY_ID = mainrec.delivery_id;
3716:
3717: UPDATE JAI_RGM_INVOICE_GEN_T
3718: SET vat_invoice_no = lv_vat_invoice_number
3719: , vat_inv_gen_status = 'C'
3720: , request_id = ln_conc_request_id
3721: , program_id = ln_conc_progam_id
3735: END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
3736:
3737: ELSE -- party not change
3738: /* 1. use existing VAT invoice number for this record;
3739: 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
3740: */
3741: IF lv_vat_invoice_number IS NOT NULL
3742: THEN
3743: -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
3747: last_update_date = sysdate,
3748: last_update_login = fnd_global.login_id,
3749: last_updated_by = fnd_global.user_id
3750: WHERE delivery_id IN (SELECT delivery_id
3751: FROM JAI_RGM_INVOICE_GEN_T jrigt
3752: WHERE party_id = ln_current_party_id
3753: AND party_site_id = ln_current_party_site_id
3754: AND party_type = mainrec.party_type
3755: AND vat_inv_gen_status <> 'C'
3767: NVL(p_order_number_to, ooha.order_number))
3768: AND trunc(delivery_Date) BETWEEN NVL(P_DELIVERY_DATE_FROM,Delivery_date)
3769: AND NVL(P_DELIVERY_DATE_TO,delivery_date));
3770:
3771: UPDATE JAI_RGM_INVOICE_GEN_T
3772: SET vat_invoice_no = lv_vat_invoice_number,
3773: vat_inv_gen_status = 'C',
3774: request_id = ln_conc_request_id,
3775: program_id = ln_conc_progam_id,
3775: program_id = ln_conc_progam_id,
3776: program_application_id = ln_conc_prog_appl_id,
3777: last_update_login = fnd_global.conc_login_id
3778: WHERE delivery_id IN (SELECT delivery_id
3779: FROM JAI_RGM_INVOICE_GEN_T jrigt
3780: WHERE party_id = ln_current_party_id
3781: AND party_site_id = ln_current_party_site_id
3782: AND party_type = mainrec.party_type
3783: AND vat_inv_gen_status <> 'C'
3806: ELSE -- single invoice number is false
3807: /* generate new VAT invoice number by document sequence;
3808: 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
3809: 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
3810: table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
3811: vat_inv_gen_status => 'C');
3812: */
3813: IF lv_Debug = 'Y' THEN
3814: Fnd_File.PUT_LINE(Fnd_File.LOG, 'In the Else when p_single_invoice is not Y ');
3856: last_update_login = fnd_global.login_id,
3857: last_updated_by = fnd_global.user_id
3858: WHERE delivery_id = mainrec.delivery_id;
3859:
3860: UPDATE JAI_RGM_INVOICE_GEN_T
3861: SET vat_invoice_no = lv_vat_invoice_number,
3862: vat_inv_gen_status = 'C',
3863: vat_inv_gen_err_message = NULL ,
3864: request_id = ln_conc_request_id,
3873: lv_inv_gen_process_message := 'No VAT Invoice Number Generated';
3874: ln_failure_delivery_ctr := NVL(ln_failure_Delivery_ctr,0) + 1;
3875: END IF; -- lv_vat_invoice_number IS NOT NULL
3876: ELSE
3877: UPDATE JAI_RGM_INVOICE_GEN_T
3878: SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
3879: vat_inv_gen_status = 'E',
3880: request_id = ln_conc_request_id,
3881: program_id = ln_conc_progam_id,
3935: WHERE order_line_id = mainrec.order_line_id
3936: AND delivery_id IS NULL;
3937:
3938: IF lv_debug = 'Y' THEN
3939: Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
3940: END IF; -- lv_debug = 'Y'
3941:
3942: UPDATE JAI_RGM_INVOICE_GEN_T
3943: SET vat_invoice_no = lv_excise_invoice_no
3938: IF lv_debug = 'Y' THEN
3939: Fnd_File.PUT_LINE(Fnd_File.LOG, ' before updating jai_rgm_invoice_gen_t for ex inv num');
3940: END IF; -- lv_debug = 'Y'
3941:
3942: UPDATE JAI_RGM_INVOICE_GEN_T
3943: SET vat_invoice_no = lv_excise_invoice_no
3944: , vat_inv_gen_status = 'C'
3945: , vat_inv_gen_err_message = NULL
3946: , request_id = ln_conc_request_id
3950: , last_update_Date = sysdate
3951: WHERE order_line_id = mainrec.order_line_id;
3952:
3953: IF lv_debug = 'Y' THEN
3954: Fnd_File.PUT_LINE(Fnd_File.LOG, ' after updating jai_rgm_invoice_gen_t for ex inv num');
3955: END IF;
3956:
3957: ln_success_delivery_Ctr := NVL(ln_success_Delivery_Ctr,0) + 1;
3958:
3978: THEN
3979: /* generate new VAT invoice number by document sequence;
3980: 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
3981: 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
3982: table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
3983: vat_inv_gen_status => 'C');
3984: */
3985: IF lv_Debug = 'Y'
3986: THEN
4023: , LAST_UPDATED_BY = fnd_global.user_id
4024: WHERE order_line_id = mainrec.order_line_id
4025: AND delivery_id IS NULL;
4026:
4027: UPDATE JAI_RGM_INVOICE_GEN_T
4028: SET vat_invoice_no = lv_vat_invoice_number
4029: , vat_inv_gen_status = 'C'
4030: , request_id = ln_conc_request_id
4031: , program_id = ln_conc_progam_id
4044: --END IF; -- lv_inv_num_already_generated = jai_constants.value_false
4045: END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
4046: ELSE -- party not change
4047: /* 1. use existing VAT invoice number for this record;
4048: 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
4049: */
4050: IF lv_vat_invoice_number IS NOT NULL THEN
4051: -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current non-shippable line.
4052: UPDATE JAI_OM_WSH_LINES_ALL
4057: , last_updated_by = fnd_global.user_id
4058: WHERE order_line_id = mainrec.order_line_id
4059: AND delivery_id IS NULL;
4060:
4061: UPDATE JAI_RGM_INVOICE_GEN_T
4062: SET vat_invoice_no = lv_vat_invoice_number
4063: , vat_inv_gen_status = 'C'
4064: , request_id = ln_conc_request_id
4065: , program_id = ln_conc_progam_id
4077: THEN
4078: /* generate new VAT invoice number by document sequence;
4079: 1. jai_cmn_rgm_setup_pkg.Gen_Invoice_number();
4080: 2. if successful, update VAT invoice number to JAI_OM_WSH_LINES_ALL, and update
4081: table JAI_RGM_INVOICE_GEN_T (vat_invoice_no => lv_vat_invoice_number,
4082: vat_inv_gen_status => 'C');
4083: */
4084: IF lv_Debug = 'Y'
4085: THEN
4122: , LAST_UPDATED_BY = fnd_global.user_id
4123: WHERE order_line_id = mainrec.order_line_id
4124: AND delivery_id IS NULL;
4125:
4126: UPDATE JAI_RGM_INVOICE_GEN_T
4127: SET vat_invoice_no = lv_vat_invoice_number
4128: , vat_inv_gen_status = 'C'
4129: , request_id = ln_conc_request_id
4130: , program_id = ln_conc_progam_id
4143: --END IF; -- lv_inv_num_already_generated = jai_constants.value_false
4144: END IF; -- p_process_action in (jai_constants.om_action_gen_inv_n_accnt ,jai_constants.om_action_gen_invoice)
4145: ELSE -- order number not change
4146: /* 1. use existing VAT invoice number for this record;
4147: 2. update JAI_OM_WSH_LINES_ALL and JAI_RGM_INVOICE_GEN_T;
4148: */
4149: IF lv_vat_invoice_number IS NOT NULL THEN
4150: -- Update the vat_invoice_num field in JAI_OM_WSH_LINES_ALL table for the current delivery.
4151: UPDATE JAI_OM_WSH_LINES_ALL
4156: , last_updated_by = fnd_global.user_id
4157: WHERE order_line_id = mainrec.order_line_id
4158: AND delivery_id IS NULL;
4159:
4160: UPDATE JAI_RGM_INVOICE_GEN_T
4161: SET vat_invoice_no = lv_vat_invoice_number
4162: , vat_inv_gen_status = 'C'
4163: , request_id = ln_conc_request_id
4164: , program_id = ln_conc_progam_id
4245: /*
4246: || Both the activities have been succesfully completed
4247: || Can commit the changes made to the delivery.
4248: */
4249: UPDATE JAI_RGM_INVOICE_GEN_T
4250: SET vat_acct_status = 'C',
4251: vat_inv_gen_err_message = NULL, /*following columns added by srjayara for bug 4702156*/
4252: request_id = ln_conc_request_id,
4253: program_id = ln_conc_progam_id,
4273: */
4274: ROLLBACK;
4275:
4276: IF lv_inv_gen_process_flag <> jai_constants.successful THEN
4277: UPDATE JAI_RGM_INVOICE_GEN_T
4278: SET vat_inv_gen_err_message = substr(lv_inv_gen_process_message,1,1000),
4279: vat_inv_gen_status = 'E',
4280: request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
4281: program_id = ln_conc_progam_id,
4289: -- modified by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4290: END IF;
4291:
4292: IF lv_acct_process_flag <> jai_constants.successful THEN
4293: UPDATE JAI_RGM_INVOICE_GEN_T
4294: SET vat_acct_err_message = substr(lv_acct_process_message,1,1000),
4295: vat_acct_status = 'E',
4296: request_id = ln_conc_request_id, /*following columns added by srjayara for bug 4702156*/
4297: program_id = ln_conc_progam_id,
4313: NULL;
4314: END LOOP;
4315:
4316: -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4317: -- need to purse JAI_RGM_INVOICE_GEN_T at the end of this concurrent
4318: DELETE FROM JAI_RGM_INVOICE_GEN_T
4319: WHERE vat_inv_gen_status = 'C'
4320: AND vat_acct_status = 'C';
4321: -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4314: END LOOP;
4315:
4316: -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), begin
4317: -- need to purse JAI_RGM_INVOICE_GEN_T at the end of this concurrent
4318: DELETE FROM JAI_RGM_INVOICE_GEN_T
4319: WHERE vat_inv_gen_status = 'C'
4320: AND vat_acct_status = 'C';
4321: -- added by Allen Yang for bug 9485355 (12.1.3 non-shippable Enhancement), end
4322: