--CREATE OR REPLACE PROCEDURE F_OB_TEST AS
declare
--p_count number;
g_request_id NUMBER := fnd_global.conc_request_id;
p_amount number ;
p_receipt_date date := to_date('29-dec-2020'); --> change in prod
p_rec_mthd number := 332091;
--p_trx_number TRX_NUMBER_TYPE;
--p_trx_amount TRX_AMT_TYPE;
P_TRX_NUMBER APPS.TRX_NUMBER_TYPE := APPS.TRX_NUMBER_TYPE();
P_TRX_AMOUNT APPS.TRX_AMT_TYPE := APPS.TRX_AMT_TYPE();
p_remittance_bank_account_id NUMBER := 112771;
p_customer_number varchar2(240);-- := '41';
p_UserName varchar2(240) := 'EG_AFZ';
W_Status varchar2(2000) ;
W_Message varchar2(2000) ;
P_COMMENTS varchar2(240) := 'OB';
p_receipt_number varchar2(240);
retcode VARCHAR(240);
l_msg_count NUMBER;
l_count NUMBER;
--l_receipt_Number NUMBER;
l_msg_data VARCHAR2 (32767);
p_count NUMBER;
l_error_mesg VARCHAR2 (32767);
l_error_code VARCHAR2 (32767);
l_org_type VARCHAR2(32767) := NULL;
l_org_name VARCHAR2(32767) := NULL;
ln_org_id NUMBER:=NULL;
l_user_id NUMBER ;--
l_resp_id NUMBER := 523322; --
l_resp_appl_id NUMBER := 222;
l_org_id NUMBER := 18113;
--l_receipt_method_id NUMBER := 7025; --METHOD_NAME = FM ??? ??? ?????
--p_receipt_method_id NUMBER := 7025; --Cash Operation; --check this later
currency_code VARCHAR2(3):='AED';
l_customer_id NUMBER;
l_site_use_id NUMBER;
l_exist VARCHAR2(32767) := 'N';
l_ErrFlag VARCHAR2(32767) := 'N';
l_ErrMsg VARCHAR2(32767) := NULL;
l_cash_receipt_id NUMBER;
l_receipt_number VARCHAR2(32767);
p_attribute_rec AR_RECEIPT_API_PUB.ATTRIBUTE_REC_TYPE;
app_attribute_rec AR_RECEIPT_API_PUB.ATTRIBUTE_REC_TYPE;
l_CustAcctId NUMBER;
l_customer_trx_id NUMBER;
l_status VARCHAR2(32767);
l_mesg VARCHAR2(32767);
l_cr_id NUMBER;
l_return_status VARCHAR2(32767);
l_payment_schedule_id NUMBER;
l_receipt_number1 NUMBER;
l_due_amt NUMBER;
l_receipt_cnt NUMBER;
l_trx_applied_details VARCHAR2(2000) := '';
cursor c1 is
select * from
XXOB_FNCE --custom table
where TXN_MODE = 'On Account Receipts '
AND CUST_NUMBER IS NOT NULL
--AND AMOUNT > '0'
AND ATTRIBUTE2 IS NULL
AND TXN_NUMBER NOT IN ('OB_F1-7')--,'OB_F1-3')
;
BEGIN
FOR I IN C1 LOOP
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name= p_UserName;
EXCEPTION
WHEN OTHERS THEN
--l_user_id:=2605;
l_ErrFlag := 'Y';
l_ErrMsg := l_ErrMsg || ' - ' || SQLERRM;
END;
/*-----------------------------------
-- Apps Initialize
--------------------------------------*/
fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_id,0);
mo_global.set_policy_context('S',l_org_id);
mo_global.init ('AR');
BEGIN
SELECT
hca.cust_account_id
INTO
l_CustAcctId
FROM
hz_cust_accounts_all hca
,hz_parties hp
WHERE
hp.party_id=hca.party_id
AND hca.account_number =I.CUST_NUMBER
AND hca.status = 'A'
AND rownum = 1
;
EXCEPTION
WHEN OTHERS THEN
l_ErrFlag := 'Y';
l_ErrMsg:='Customer Does Not Exists';
retcode:=0;----------Added on 21-aug-2017 Requested by NBF.
---l_ErrMsg := l_ErrMsg || ' - ' || SQLERRM;
l_ErrMsg := l_ErrMsg || ' - ' || SQLERRM;
W_Status:='Failure';
W_Message:=l_ErrMsg||'_'||'APPLY API';
FND_FILE.PUT_LINE(FND_FILE.LOG,
'ERROR: while fetching Customer_Number for Cust Ref ' || ' ' || SQLERRM);
END;
fnd_file.put_line(fnd_file.log,'Customer_Number' ||p_Customer_Number);
fnd_file.put_line(fnd_file.log,'l_CustAcctId' ||l_CustAcctId);
DBMS_OUTPUT.PUT_LINE('l_CustAcctId'||l_CustAcctId);
IF l_CustAcctId >0 THEN
BEGIN
SELECT c.site_use_id
---a.cust_account_id,
--- e.address1,
---e.address2,
---e.address3,
--- e.address4,
--e.postal_code,
--- e.province,
-- e.state,
--- country
INTO
l_site_use_id
FROM hz_cust_accounts a,
hz_cust_acct_sites_all b,
hz_cust_site_uses_all c,
hz_party_sites d,
hz_locations e
WHERE a.cust_account_id = b.cust_account_id
AND b.cust_acct_site_id = c.cust_acct_site_id
AND b.party_site_id = d.party_site_id
AND d.location_id = e.location_id
AND c.site_use_code = 'BILL_TO'
AND c.status = 'A'
AND b.org_id = c.org_id
AND b.org_id =l_org_id
AND rownum = 1
AND a.cust_account_id=l_CustAcctId;
EXCEPTION
WHEN OTHERS THEN
l_ErrFlag := 'Y';
l_ErrMsg:='Customer Sits is having Issue';
retcode:=0;----------Added on 21-aug-2017 Requested by NBF.
l_ErrMsg := l_ErrMsg || ' - ' || SQLERRM;
W_Status:='Failure';
W_Message:=l_ErrMsg||'_'||'APPLY API';
FND_FILE.PUT_LINE(FND_FILE.LOG,
'ERROR: while fetching Bill To '|| ' ' || SQLERRM);
END;
DBMS_OUTPUT.PUT_LINE('l_org_id'||l_org_id);
DBMS_OUTPUT.PUT_LINE('l_site_use_id'||l_site_use_id);
/******************************************************************************************************
-----------END OF Bill To Validations
******************************************************************************************************/
IF l_site_use_id> 0 THEN ---Commented by Rakesh 23-Aug-217
p_attribute_rec.ATTRIBUTE5 := '1';
BEGIN
--l_receipt_Number := 'XX-11';
DBMS_OUTPUT.PUT_LINE('Calling Create Cash');
BEGIN
AR_RECEIPT_API_PUB.Create_Apply_on_acc
( p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_TRUE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'AED',
p_amount => ABS(i.amount),
p_receipt_number => i.txn_number,
p_receipt_date => TO_DATE('29-dec-2020'),
p_gl_date => TO_DATE('29-dec-2020'),
--p_customer_number => v_customer_number,
p_customer_id => l_CustAcctId,
p_receipt_method_id => p_rec_mthd,
p_org_id => l_org_id,
p_cr_id => l_cr_id,
p_attribute_rec =>p_attribute_rec
);
DBMS_OUTPUT.PUT_LINE('STATUS '||l_return_status||' x_msg_count '||l_msg_count||' x_msg_data '||l_msg_data);
IF l_return_status = 'S' THEN
DBMS_OUTPUT.put_line('Receipt Creation and apply on account is Sucessful :');
ELSE
DBMS_OUTPUT.put_line('Message count ' || L_msg_count);
IF L_msg_count = 1 THEN
DBMS_OUTPUT.put_line('v_msg_data '||L_msg_data);
ELSIF L_msg_count > 1 THEN
LOOP
p_count := p_count+1;
L_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF l_msg_data IS NULL THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line('Message' || p_count ||'---'||L_msg_data);
END LOOP;
END IF;
END IF;
END;
-- end if;
COMMIT;
UPDATE XXOB_FNCE
SET ATTRIBUTE2 = l_return_status
WHERE TXN_NUMBER = I.TXN_NUMBER;
COMMIT;
END;
------------END OF create cash
-- END IF; --EXIST
-- END IF;----l_receipt_cnt
ELSE
W_Status:='Failure';
W_Message:=l_ErrMsg||'_'||'Customer_SITE Does Not Exist';
retcode:=0;------------Added on 21-aug-2017 Requested by NBF.---Commented by Rakesh 23-Aug-217
DBMS_OUTPUT.put_line ('retcode ' || retcode);
END IF; ---l_site_use_id---Commented by Rakesh 23-Aug-217
ELSE
W_Status:='Failure';
W_Message:=l_ErrMsg||'_'||'Customer_Number Does Not Exist';
retcode:=0;----------Added on 21-aug-2017 Requested by NBF.
DBMS_OUTPUT.put_line ('W_Status ' || W_Status);
DBMS_OUTPUT.put_line ('W_Message ' || W_Message);
DBMS_OUTPUT.put_line ('retcode ' || retcode);
END IF; ---l_CustAcctId
--arp_standard.disable_debug;
END LOOP;
END ;--create_rece
-- END ; --
/
Wednesday, January 6, 2021
Oracle Apps R12 create AR Receipt as On Account using api AR_RECEIPT_API_PUB.Create_Apply_on_acc
Subscribe to:
Posts (Atom)