Home » SQL & PL/SQL » SQL & PL/SQL » Need Help In Plsql Procedure (Plsql)
Need Help In Plsql Procedure [message #669307] |
Sat, 14 April 2018 17:25 |
Djpats
Messages: 17 Registered: January 2018
|
Junior Member |
|
|
HI Guys, I m new to Pl/sql need your help
below is my procedure code
My requirement is Either I want to pass PV_CRN_NEW_PLAN value to procedure OR PV_USER_AMOUNT value to procedure..
I can not pass both the values at a time.
( I am handling errors by using 'raise_application_error' is there any other way to handle the errors);
how can I handle this issues.
any quick help will be appreciated...
thnxxx in advance...
below is a procedure code for the reference.....
PROCEDURE prc_test11 (P_CRN_CIRCUIT_ID VARCHAR2,
P_CRN_BILL_FROM_DATE DATE,
P_CRN_BILL_TO_DATE DATE,
PV_CRN_OPEN_INV_NO VARCHAR2,
PV_CRN_REASON VARCHAR2,
PV_CRN_EFFECTIVE_PERIOD_FRM DATE,
PV_CRN_EFFECTIVE_PERIOD_TO DATE,
PV_CRN_NEW_PLAN NUMBER,
PV_USER_AMOUNT NUMBER)
IS
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_user_id NUMBER;
l_dtl_cnt NUMBER;
l_cnt NUMBER;
l_first_day DATE;
l_last_day DATE;
l_5th_day DATE;
l_prov_amount NUMBER;
l_new_prov_amount NUMBER;
l_batch_number NUMBER;
L_CRN_REVISED_INV_AMT NUMBER;
L_CRN_CREDIT_AMT NUMBER;
L_CRN_RECURRING_CHARGE NUMBER;
L_CRN_OTC NUMBER;
L_CRN_USAGE_CHARGE NUMBER;
L_CRN_MISC_CREDIT_CHARGE NUMBER;
L_RECUARRING_CHARGES NUMBER;
L_ONETIME_CHARGES NUMBER;
L_USAGE_CHARGES NUMBER;
L_MISC_CHARGES NUMBER;
L_TOTAL_TAX_EX NUMBER;
L_AMT_IN_WORDS VARCHAR2 (250);
P_LOCATION_ID NUMBER;
P_ADDRESS1 VARCHAR2 (32767);
P_ADDRESS2 VARCHAR2 (32767);
P_ADDRESS3 VARCHAR2 (32767);
P_CIRCLE VARCHAR2 (32767);
P_CIRCLE2 VARCHAR2 (32767);
P_POSTAL_CODE VARCHAR2 (32767);
P_CITY VARCHAR2 (32767);
P_STATE VARCHAR2 (32767);
P_COUNTRY VARCHAR2 (32767);
P_LANDMARK VARCHAR2 (32767);
P_PHONE_NUMBER VARCHAR2 (32767);
P_EMPCODE VARCHAR2 (32767);
P_PERSON_FIRST_NAME VARCHAR2 (32767);
P_PERSON_MIDDLE_NAME VARCHAR2 (32767);
P_PERSON_LAST_NAME VARCHAR2 (32767);
P_DESIGNATION VARCHAR2 (32767);
P_DEPARTMENT VARCHAR2 (32767);
P_FAX_NUMBER VARCHAR2 (32767);
P_ALT_NUMBER VARCHAR2 (32767);
P_EMAIL_ADDRESS VARCHAR2 (32767);
P_VEHICLE_NO VARCHAR2 (32767);
P_COMPANY_PAN_NO VARCHAR2 (32767);
P_TABLE_NAME VARCHAR2 (250);
L_CRN_OPEN_INV_AMT NUMBER;
L_CRN_DETAIL_ID NUMBER;
L_CRN_NOTE_ID NUMBER;
L_CREATED_BY NUMBER;
L_CREATION_DATE DATE;
L_LAST_UPDATED_BY NUMBER;
L_LAST_UPDATE_DATE DATE;
V_CLOSE_INV_NO VARCHAR2 (250);
V_ACT_INV_AMOUNT NUMBER;
CURSOR c1
IS
SELECT DISTINCT a.enterprise_code,
d.address1,
a.customer_id,
d.city,
d.postal_code,
d.state,
d.country,
e.party_name,
a.subscriber_id,
a.product_type,
e.attribute10,
e.attribute2,
a.resource_id,
a.table_name,
c.location_id,
a.FA_NUMBER,
a.STATUS
FROM xxvfent_all_caf_header_v a,
hz_cust_acct_sites_all b,
hz_party_sites c,
hz_locations d,
hz_parties e
WHERE a.resource_id = P_CRN_CIRCUIT_ID
AND a.PRODUCT IN ('Internet Leased Lines',
'MPLS',
'NPLC',
'IPLC')
--AND a.STATUS = 'CLOSED'
AND a.customer_id = b.cust_account_id
AND ( a.resource_id = b.attribute1
OR a.ba_number = b.attribute2)
AND a.party_id = c.party_id
AND a.party_id = e.party_id
AND b.party_site_id = c.party_site_id
AND c.location_id = d.location_id;
CURSOR c2
IS
SELECT DISTINCT
LEGAL_INVOICE_NO,
(CASE
WHEN INVOICE_STATUS = 'O' THEN 'Open'
WHEN INVOICE_STATUS = 'C' THEN 'Close'
ELSE 'Other'
END)
INVOICE_STATUS,
STATEMENT_DATE,
INVOICE_AMOUNT,
AMOUNT_CURRENCY,
START_DATE,
END_DATE,
INVOICE_ID,
ACCOUNT_ID
FROM XXVFENT_CREDIT_NOTE_ALL_INV_V
WHERE prim_resource_val = P_CRN_CIRCUIT_ID
AND ( START_DATE >= P_CRN_BILL_FROM_DATE
AND END_DATE <= P_CRN_BILL_TO_DATE)
AND LEGAL_INVOICE_NO = PV_CRN_OPEN_INV_NO;
r1 c1%ROWTYPE;
r2 c2%ROWTYPE;
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 xxvfent_credit_note_details
WHERE crn_note_id = L_crn_note_id;
SELECT COUNT (1)
INTO l_cnt
FROM xxvfent_credit_note_details
WHERE crn_circuit_id = p_crn_circuit_id
AND crn_reason = PV_crn_reason
AND TRUNC (creation_date) BETWEEN l_first_day AND l_last_day
AND crn_open_inv_no = PV_crn_open_inv_no;
BEGIN
SELECT balance_amount, batch_number
INTO l_prov_amount, l_batch_number
FROM hutchcs.xxvfent_credit_note_bulk_load
WHERE circuit_id = p_crn_circuit_id
AND batch_number =
(SELECT MAX (batch_number)
FROM hutchcs.xxvfent_credit_note_bulk_load
WHERE circuit_id = p_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 l_dtl_cnt = 0
THEN
IF l_cnt > 0
THEN
raise_application_error (
-20010,
'Cannot create Credit Note for the same Circuit against same Invoice in the Calendar Month. Please select a different Invoice Number.');
END IF;
END IF;
BEGIN
P_LOCATION_ID := NULL;
P_TABLE_NAME := NULL;
P_ADDRESS1 := NULL;
P_ADDRESS2 := NULL;
P_ADDRESS3 := NULL;
P_CIRCLE := NULL;
P_CIRCLE2 := NULL;
P_POSTAL_CODE := NULL;
P_CITY := NULL;
P_STATE := NULL;
P_COUNTRY := NULL;
P_LANDMARK := NULL;
P_PHONE_NUMBER := NULL;
P_EMPCODE := NULL;
P_PERSON_FIRST_NAME := NULL;
P_PERSON_MIDDLE_NAME := NULL;
P_PERSON_LAST_NAME := NULL;
P_DESIGNATION := NULL;
P_DEPARTMENT := NULL;
P_FAX_NUMBER := NULL;
P_ALT_NUMBER := NULL;
P_EMAIL_ADDRESS := NULL;
P_VEHICLE_NO := NULL;
P_COMPANY_PAN_NO := NULL;
APPS.XXVFENT_CUSTOMER_CAF_PKG.XXVFENT_CUST_LOC_FETCH (
P_LOCATION_ID,
P_ADDRESS1,
P_ADDRESS2,
P_ADDRESS3,
P_CIRCLE,
P_CIRCLE2,
P_POSTAL_CODE,
P_CITY,
P_STATE,
P_COUNTRY,
P_LANDMARK,
P_PHONE_NUMBER,
P_EMPCODE,
P_PERSON_FIRST_NAME,
P_PERSON_MIDDLE_NAME,
P_PERSON_LAST_NAME,
P_DESIGNATION,
P_DEPARTMENT,
P_FAX_NUMBER,
P_ALT_NUMBER,
P_EMAIL_ADDRESS,
P_VEHICLE_NO,
P_COMPANY_PAN_NO);
END;
IF PV_CRN_NEW_PLAN IS NOT NULL
THEN
APPS.xxvfent_credit_note_pkg.revised_invoice_amount (
p_invoice_no => PV_CRN_OPEN_INV_NO,
p_reason => PV_CRN_REASON,
p_effective_from_date => TRUNC (PV_CRN_EFFECTIVE_PERIOD_FRM),
p_effective_to_date => TRUNC (PV_CRN_EFFECTIVE_PERIOD_TO),
p_new_plan_amount => PV_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);
ELSIF PV_USER_AMOUNT IS NOT NULL
THEN
APPS.xxvfent_credit_note_pkg.user_revised_invoice_amount (
p_invoice_no => PV_CRN_OPEN_INV_NO,
p_reason => PV_CRN_REASON,
p_effective_from_date => TRUNC (PV_CRN_EFFECTIVE_PERIOD_FRM),
p_effective_to_date => TRUNC (PV_CRN_EFFECTIVE_PERIOD_TO),
p_user_amount => PV_USER_AMOUNT,
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);
END IF;
IF v_status = 'S'
THEN
IF v_credit_amount > L_CRN_OPEN_INV_AMT
THEN
raise_application_error (
-20011,
'Credit Amount should not be greater than the Open Invoice Amount. Credit Amount is '
|| v_credit_amount
|| ' and Open Invoice Amount is '
|| L_CRN_OPEN_INV_AMT);
END IF;
IF v_credit_amount <= 0
THEN
raise_application_error (
-20012,
'Credit Amount should not be less than or equal to 0');
END IF;
IF l_batch_number = 0
THEN
raise_application_error (
-20013,
'Provision is not loaded for this Circuit ID. Please Load Circuit and then procced.');
END IF;
l_new_prov_amount := l_prov_amount + ( (l_prov_amount / 100) * 10);
IF v_credit_amount > l_new_prov_amount
THEN
raise_application_error (
-20014,
'Credit Amount should not be greater than Provision with 10% variation. Credit Amount is :'
|| v_credit_amount
|| ' and Provision Amount is '
|| l_prov_amount);
ELSE
IF v_credit_amount <= l_prov_amount
THEN
UPDATE hutchcs.xxvfent_credit_note_bulk_load
SET balance_amount = balance_amount - v_credit_amount
WHERE circuit_id = P_CRN_CIRCUIT_ID
AND batch_number = l_batch_number;
ELSE
UPDATE hutchcs.xxvfent_credit_note_bulk_load
SET balance_amount = 0
WHERE circuit_id = P_crn_circuit_id
AND batch_number = l_batch_number;
END IF;
COMMIT;
END IF;
L_CRN_REVISED_INV_AMT := v_revised_invoice_amount;
L_CRN_CREDIT_AMT := v_credit_amount;
L_CRN_RECURRING_CHARGE := v_recurring_charge;
L_CRN_OTC := v_otc;
L_CRN_USAGE_CHARGE := v_usage_charge;
L_CRN_MISC_CREDIT_CHARGE := v_credit_charge;
v_total_wo_tax :=
v_recurring_charge + v_otc + v_usage_charge + v_credit_charge;
L_RECUARRING_CHARGES := v_recurring_charge;
L_ONETIME_CHARGES := v_otc;
L_USAGE_CHARGES := v_usage_charge;
L_MISC_CHARGES := v_credit_charge;
L_TOTAL_TAX_EX := v_total_wo_tax;
L_AMT_IN_WORDS := v_in_words;
SELECT user_id
INTO l_user_id
FROM apps.fnd_user
WHERE user_name = 'SFADMIN'; --FND_PROFILE.VALUE(USERNAME);
SELECT DISTINCT LEGAL_INVOICE_NO, INVOICE_AMOUNT
INTO V_CLOSE_INV_NO, V_ACT_INV_AMOUNT
FROM xxvfent_credit_no_closed_inv_v
WHERE prim_resource_val = P_CRN_CIRCUIT_ID
AND ( START_DATE >= P_CRN_BILL_FROM_DATE
AND END_DATE <= P_CRN_BILL_TO_DATE);
--Genearet Sequence Numbers
SELECT credit_note_seq.NEXTVAL INTO v_credit_note_no FROM DUAL;
SELECT xxvfent_credit_note_dtl_seq.NEXTVAL INTO l_dtl_seq FROM DUAL;
L_CRN_DETAIL_ID := l_dtl_seq;
L_CRN_NOTE_ID := v_credit_note_no;
L_CREATED_BY := l_user_id;
L_CREATION_DATE := SYSDATE;
L_LAST_UPDATED_BY := l_user_id;
L_LAST_UPDATE_DATE := SYSDATE;
SELECT xxvfent_credit_note_hdr_seq.NEXTVAL INTO l_hdr_seq FROM DUAL;
INSERT INTO HUTCHCS.XXVFENT_CREDIT_NOTE_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_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,
r1.product_type,
P_CRN_CIRCUIT_ID,
TRUNC (SYSDATE),
P_CRN_BILL_FROM_DATE,
P_CRN_BILL_TO_DATE,
r1.ENTERPRISE_CODE,
r1.PARTY_NAME,
r1.SUBSCRIBER_ID,
r1.ATTRIBUTE10,
r1.attribute2,
P_PHONE_NUMBER,
P_ADDRESS1,
P_POSTAL_CODE,
P_COUNTRY,
P_STATE,
0,
r1.enterprise_code,
r1.FA_NUMBER,
r1.STATUS,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE);
FOR r2 IN c2
LOOP
BEGIN
INSERT
INTO HUTCHCS.XXVFENT_CREDIT_NOTE_DETAILS (
CRN_DETAIL_ID,
CRN_NOTE_ID,
CRN_CIRCUIT_ID,
CRN_REASON,
CRN_CLOSE_INV_NO,
CRN_ACT_INV_AMT,
CRN_NEW_PLAN,
CRN_EFFECTIVE_PERIOD_FRM,
CRN_EFFECTIVE_PERIOD_TO,
CRN_OPEN_INV_NO,
CRN_INV_DATE,
CRN_OPEN_INV_AMT,
CRN_REVISED_INV_AMT,
CRN_CREDIT_AMT,
CRN_RECURRING_CHARGE,
CRN_OTC,
CRN_USAGE_CHARGE,
CRN_MISC_CREDIT_CHARGE,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
VALUES (L_CRN_DETAIL_ID,
L_CRN_NOTE_ID,
P_CRN_CIRCUIT_ID,
PV_CRN_REASON,
V_CLOSE_INV_NO,
V_ACT_INV_AMOUNT,
PV_CRN_NEW_PLAN,
PV_CRN_EFFECTIVE_PERIOD_FRM,
PV_CRN_EFFECTIVE_PERIOD_TO,
PV_CRN_OPEN_INV_NO,
r2.STATEMENT_DATE,
r2.INVOICE_AMOUNT,
L_CRN_REVISED_INV_AMT,
L_CRN_CREDIT_AMT,
L_RECUARRING_CHARGES,
L_ONETIME_CHARGES,
L_USAGE_CHARGES,
L_MISC_CHARGES,
r2.INVOICE_ID,
r2.ACCOUNT_ID,
PV_USER_AMOUNT,
r2.AMOUNT_CURRENCY,
r2.INVOICE_STATUS,
l_user_id,
SYSDATE,
l_user_id,
SYSDATE);
END;
END LOOP;
COMMIT;
ELSE
v_status := 'E';
raise_application_error (-20017, 'Error Occured ');
END IF;
END prc_test11;
|
|
|
|
|
Re: Need Help In Plsql Procedure [message #669331 is a reply to message #669312] |
Mon, 16 April 2018 03:23 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
If PV_CRN_NEW_PLAN IS NOT NUL AND PV_USER_AMOUNT IS NOT NULL THEN
raise_application_error (<error code>, 'you can only supply one of PV_CRN_NEW_PLAN and PV_USER_AMOUNT);
Concurrent program is an oracle apps thing - yoou'd better off asking in the apps forums about that.
Some other comments on the code:
1) Setting local variables to null at the start is a waste of time. They'll be null if you don't do anything.
2) select from dual is almost never needed in PL/SQL eg.
SELECT TRUNC (LAST_DAY (SYSDATE)) INTO l_last_day FROM DUAL;
should be:
l_last_day := TRUNC (LAST_DAY (SYSDATE));
There are a handful of SQL functions that don't work in PL/SQL and for those you'll need select from dual, but not otherwise.
3) SELECT COUNT(1) should be SELECT COUNT(*)
4) The final for loop/insert will be more performant written as an insert/select.
5) You've got unused variables.
6) Variables should be typed to columns where ever possible:
l_prov_amount xxvfent_credit_note_bulk_load.balance_amount%TYPE
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:49:16 CDT 2024
|