DECLARE CURRENT_REC_NO NUMBER (3) := NULL; v_credit_note_no NUMBER; v_revised_invoice_amount NUMBER; v_credit_amount NUMBER; v_recurring_charge NUMBER; v_otc NUMBER; v_usage_charge NUMBER; v_credit_charge NUMBER; v_in_words VARCHAR2 (2000); v_status VARCHAR2 (10); v_message VARCHAR2 (2000); v_total_wo_tax NUMBER; l_hdr_seq NUMBER; l_dtl_Seq NUMBER; l_auth_seq NUMBER; l_user_id NUMBER; l_record_no NUMBER; l_status VARCHAR2 (250); l_dtl_cnt NUMBER; l_prov_cnt NUMBER; l_alert alert; l_id NUMBER; l_text VARCHAR2 (250); l_cnt NUMBER; l_first_day DATE; l_last_day DATE; l_5th_day DATE; l_inv_status VARCHAR2 (50); l_prov_amount NUMBER; l_new_prov_amount NUMBER; l_batch_number NUMBER; BEGIN SELECT TRUNC (SYSDATE, 'Month') INTO l_first_day FROM DUAL; SELECT TRUNC (SYSDATE) - (TO_NUMBER (TO_CHAR (SYSDATE, 'DD')) - 5) INTO l_5th_day FROM DUAL; SELECT TRUNC (LAST_DAY (SYSDATE)) INTO l_last_day FROM DUAL; SELECT COUNT (1) INTO l_dtl_cnt FROM XX_CR_DETAILS WHERE crn_note_id = :XX_CR_DETAILS.crn_note_id; SELECT COUNT (1) INTO l_cnt FROM XX_CR_DETAILS WHERE crn_circuit_id = :CONTROL_BLOCK.crn_circuit_id AND crn_reason = :XX_CR_DETAILS.crn_reason AND TRUNC (creation_date) BETWEEN l_first_day AND l_last_day AND crn_open_inv_no = :XX_CR_DETAILS.crn_open_inv_no; BEGIN SELECT balance_amount, batch_number INTO l_prov_amount, l_batch_number FROM HT.XX_credit_note_prov_load WHERE circuit_id = :CONTROL_BLOCK.crn_circuit_id AND batch_number = (SELECT MAX (batch_number) FROM HT.XX_credit_note_prov_load WHERE circuit_id = :CONTROL_BLOCK.crn_circuit_id AND file_upload_date BETWEEN l_first_day AND l_last_day); EXCEPTION WHEN OTHERS THEN l_prov_amount := 0; l_batch_number := 0; END; IF TRUNC (SYSDATE) BETWEEN l_first_day AND l_5th_day THEN l_alert := FIND_ALERT ('ERROR'); l_text := 'Cannot create Credit Note between 1st to 5th in the Calendar Month.'; SET_ALERT_PROPERTY (l_alert, alert_message_text, l_text); l_id := SHOW_ALERT (l_alert); RAISE Form_Trigger_Failure; END IF; IF l_dtl_cnt = 0 THEN IF l_cnt > 0 THEN l_alert := FIND_ALERT ('ERROR'); l_text := 'Cannot create Credit Note for the same Circuit against same Invoice in the Calendar Month.'; SET_ALERT_PROPERTY (l_alert, alert_message_text, l_text); l_id := SHOW_ALERT (l_alert); RAISE Form_Trigger_Failure; END IF; END IF; effective_dates_validation; --GO_BLOCK ('XX_CR_DETAILS'); APPS.XX_credit_note_pkg.revised_invoice_amount ( p_invoice_no => :XX_CR_DETAILS.CRN_OPEN_INV_NO, p_reason => :XX_CR_DETAILS.CRN_REASON, p_effective_from_date => TRUNC ( :XX_CR_DETAILS.CRN_EFFECTIVE_PERIOD_FRM), p_effective_to_date => TRUNC ( :XX_CR_DETAILS.CRN_EFFECTIVE_PERIOD_TO), p_new_plan_amount => :XX_CR_DETAILS.CRN_NEW_PLAN, p_revised_invoice_amount => v_revised_invoice_amount, p_credit_amount => v_credit_amount, p_recurring_charge => v_recurring_charge, p_otc => v_otc, p_usage_charge => v_usage_charge, p_credit_charge => v_credit_charge, p_in_words => v_in_words, p_status => v_status, p_message => v_message); IF v_status = 'S' THEN IF v_credit_amount > :XX_CR_DETAILS.CRN_OPEN_INV_AMT THEN l_alert := FIND_ALERT ('ERROR'); l_text := 'Credit Amount should not be greater than the Open Invoice Amount. Credit Amount is ' || v_credit_amount || ' and Open Invoice Amount is ' || :XX_CR_DETAILS.CRN_OPEN_INV_AMT; SET_ALERT_PROPERTY (l_alert, alert_message_text, l_text); l_id := SHOW_ALERT (l_alert); RAISE Form_Trigger_Failure; END IF; IF v_credit_amount <= 0 THEN l_alert := FIND_ALERT ('ERROR'); l_text := 'Credit Amount should not be less than or equal to 0.'; SET_ALERT_PROPERTY (l_alert, alert_message_text, l_text); l_id := SHOW_ALERT (l_alert); RAISE Form_Trigger_Failure; END IF; IF l_batch_number = 0 THEN l_alert := FIND_ALERT ('ERROR'); l_text := 'Provision is not loaded for this Circuit ID. Please Load Circuit and then procced. '; SET_ALERT_PROPERTY (l_alert, alert_message_text, l_text); l_id := SHOW_ALERT (l_alert); RAISE Form_Trigger_Failure; END IF; l_new_prov_amount := l_prov_amount + ( (l_prov_amount / 100) * 10); IF v_credit_amount > l_new_prov_amount THEN l_alert := FIND_ALERT ('ERROR'); l_text := 'Credit Amount should not be greater than Provision Amount with 10% variation. Credit Amount is :' || v_credit_amount || ' and Provision Amount is ' || l_prov_amount; SET_ALERT_PROPERTY (l_alert, alert_message_text, l_text); l_id := SHOW_ALERT (l_alert); RAISE Form_Trigger_Failure; ELSE IF v_credit_amount <= l_prov_amount THEN UPDATE HT.XX_credit_note_prov_load SET balance_amount = balance_amount - v_credit_amount WHERE circuit_id = :control_block.crn_circuit_id AND batch_number = l_batch_number; ELSE UPDATE HT.XX_credit_note_prov_load SET balance_amount = 0 WHERE circuit_id = :control_block.crn_circuit_id AND batch_number = l_batch_number; END IF; COMMIT; END IF; :XX_CR_DETAILS.CRN_REVISED_INV_AMT := v_revised_invoice_amount; :XX_CR_DETAILS.CRN_CREDIT_AMT := v_credit_amount; :XX_CR_DETAILS.CRN_RECURRING_CHARGE := v_recurring_charge; :XX_CR_DETAILS.CRN_OTC := v_otc; :XX_CR_DETAILS.CRN_USAGE_CHARGE := v_usage_charge; :XX_CR_DETAILS.CRN_MISC_CREDIT_CHARGE := v_credit_charge; v_total_wo_tax := v_recurring_charge + v_otc + v_usage_charge + v_credit_charge; :INV_DETAILS.ONETIME_CHARGES := v_otc; :INV_DETAILS.USAGE_CHARGES := v_usage_charge; :INV_DETAILS.MISC_CHARGES := v_credit_charge; :INV_DETAILS.RECUARRING_CHARGES := v_recurring_charge; :INV_DETAILS.ONETIME_CHARGES := v_otc; :INV_DETAILS.USAGE_CHARGES := v_usage_charge; :INV_DETAILS.MISC_CHARGES := v_credit_charge; :INV_DETAILS.TOTAL_TAX_EX := v_total_wo_tax; :INV_DETAILS.AMT_IN_WORDS := v_in_words; END IF; SELECT user_id INTO l_user_id FROM apps.fnd_user WHERE user_name = :parameter.p_user_name; l_status := GET_BLOCK_PROPERTY ('XX_CR_DETAILS', STATUS); IF ( :XX_CR_DETAILS.CRN_NOTE_ID IS NULL OR :XX_CR_DETAILS.CRN_DETAIL_ID IS NULL) THEN --Genearet Sequence Numbers SELECT credit_note_seq.NEXTVAL INTO v_credit_note_no FROM DUAL; SELECT XX_credit_note_dtl_seq.NEXTVAL INTO l_dtl_seq FROM DUAL; :XX_CR_DETAILS.CRN_DETAIL_ID := l_dtl_seq; :XX_CR_DETAILS.CRN_NOTE_ID := v_credit_note_no; :XX_CR_DETAILS.CREATED_BY := l_user_id; :XX_CR_DETAILS.CREATION_DATE := SYSDATE; :XX_CR_DETAILS.LAST_UPDATED_BY := l_user_id; :XX_CR_DETAILS.LAST_UPDATE_DATE := SYSDATE; SELECT XX_credit_note_hdr_seq.NEXTVAL INTO l_hdr_seq FROM DUAL; INSERT INTO HT.XX_CR_HEADER (CRN_HEADER_ID, CRN_NOTE_ID, CRN_BUSI_TYPE, CRN_CIRCUIT_ID, CRN_CREATED_DATE, CRN_BILL_FROM_DATE, CRN_BILL_TO_DATE, CUST_CODE, CUST_NAME, CUST_TYPE, CUST_SUBSCRIBER_ID, CUST_CIRCLE_ID, CUST_SEGMENT, CUST_CONTACT_NO, CUST_ADDRESS, CUST_POSTCODE, CUST_COUNTRY, CUST_STATE, ACCOUNT_ID, ENTERPRISE_CODE, FA_ID, CIRCUIT_STATUS, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE) VALUES (l_hdr_seq, v_credit_note_no, :CONTROL_BLOCK.CRN_BUSI_TYPE, :CONTROL_BLOCK.CRN_CIRCUIT_ID, TRUNC (SYSDATE), :CONTROL_BLOCK.CRN_BILL_FROM_DATE, :CONTROL_BLOCK.CRN_BILL_TO_DATE, :CONTROL_BLOCK.CUST_CODE, :CONTROL_BLOCK.CUST_NAME, :CONTROL_BLOCK.CUST_TYPE, :CONTROL_BLOCK.CUST_SUBSCRIBER_ID, :CONTROL_BLOCK.CUST_CIRCLE_ID, :CONTROL_BLOCK.CUST_SEGMENT, :CONTROL_BLOCK.CUST_CONTACT_NO, :CONTROL_BLOCK.CUST_ADDRESS, :CONTROL_BLOCK.CUST_POSTCODE, :CONTROL_BLOCK.CUST_COUNTRY, :CONTROL_BLOCK.CUST_STATE, 0, :CONTROL_BLOCK.ENTERPRISE_CODE, :CONTROL_BLOCK.FA_NUMBER, :CONTROL_BLOCK.STATUS, l_user_id, SYSDATE, l_user_id, SYSDATE); ELSE :XX_CR_DETAILS.LAST_UPDATED_BY := l_user_id; :XX_CR_DETAILS.LAST_UPDATE_DATE := SYSDATE; END IF; COMMIT_FORM; END;