CREATE OR REPLACE PROCEDURE APPS.XX_CREATE_CASH_RECEIPT_APPLY(errbuf out NOCOPY varchar2,
retcode out NOCOPY varchar2)
IS
L_RETURN_STATUS VARCHAR2(240);
L_MSG_COUNT NUMBER;
L_MSG_DATA VARCHAR2(240);
L_CASH_RECEIPT_ID NUMBER;
v_customer_number VARCHAR2(240);
v_cust_name VARCHAR(240);
v_amount NUMBER;
v_receipt_number NUMBER;



CURSOR C1
IS
SELECT * FROM XX_AR_RECEIPTS_GMC;

BEGIN

BEGIN
MO_GLOBAL.SET_POLICY_CONTEXT(‘S’,150);
END;


FOR I IN C1 LOOP

BEGIN

v_cust_name := I.customer_name; –substr(I.CUSTOMER_NAME,1,length(I.CUSTOMER_NAME)-1);


SELECT DISTINCT ARC.CUSTOMER_NUMBER
INTO v_customer_number
FROM AR_CUSTOMERS ARC
,HZ_CUST_ACCOUNTS_ALL HCA
,HZ_CUST_ACCT_SITES_ALL HCAS
WHERE HCA.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID
AND HCA.CUST_ACCOUNT_ID = ARC.CUSTOMER_ID
AND HCAS.ORG_ID = 150
AND LTRIM(RTRIM(UPPER(ARC.CUSTOMER_NAME))) = LTRIM(RTRIM(UPPER(v_cust_name)));


DBMS_OUTPUT.PUT_LINE (‘Customer Id – ‘||v_customer_number);


EXCEPTION
WHEN NO_DATA_FOUNd THEN
DBMS_OUTPUT.PUT_LINE(I.CUSTOMER_NAME||’ Customer Error: ‘||SUBSTR(SQLERRM,1,150));
END;


v_amount:= to_number(substr(I.AMOUNT,1,length(I.AMOUNT)-1));
–v_amount := to_number(I.AMOUNT);
v_receipt_number := to_number(I.RECEIPT_NUMBER);


AR_RECEIPT_API_PUB.create_cash
( 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 => v_amount,
p_receipt_number => v_receipt_number,
p_receipt_date => sysdate,
p_gl_date => to_date(’31-dec-2008′),
p_customer_number => v_customer_number,
p_org_id => 150,
p_receipt_method_id => ‘2007’,
p_cr_id => l_cash_receipt_id);



/* AR_RECEIPT_API_PUB.create_and_apply
( 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_amount => v_amount,
p_receipt_number => v_receipt_number,
p_receipt_date => sysdate,
p_gl_date => to_date(’31-dec-2008′),
p_customer_number => v_customer_number,
p_location => ‘Abu Dhabi’,
p_receipt_method_id => ‘2007’,
p_trx_number => ‘500001’,
p_cr_id => l_cash_receipt_id
);
*/


END LOOP;


DBMS_OUTPUT.PUT_LINE(‘Cash Receipt Created & Applied’||’-‘||l_cash_receipt_id||’- Comments : ‘||l_msg_data||l_return_status);


COMMIT;


EXCEPTION
WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,150)||’-‘||l_msg_data);

END;
/