CREATE OR REPLACE PACKAGE BODY APPS.cmg_incen_epay_to_ap_pck
/*
|| CMG INCENTIVE EPAYMENTS TO AP
||
|| Revision Author Date Modified Description
|| 1.0 UDAY T 7/18/12 Initial revision.
*/
AS
-- Global Variables for the package body
error_msg VARCHAR2 (1000) := NULL;
output_message VARCHAR2 (1000) := NULL;
PROCEDURE cmg_incen_epay_to_ap_main_prc (
errbuf IN VARCHAR2,
retcode IN VARCHAR2,
P_DATE IN varchar2
)
AS
/* RUN FOR A SPECIFIC PARTY_NUMBER WHICH IS DERIVED FROM INCENTIVES ALSO CALLED AS RDA_RDP_ACCOUNT_NUMBER */
CURSOR EPAY_CUR (P_DATE IN varchar2)IS
SELECT V.PARTY_NAME HEADER_PAYEE_NAME,
TRUNC (V.REMITTANCE_DATE),
V.PARTY_NUMBER ,
V.PARTY_NAME PAYEE_NAME,
-- p.PARTY_NAME,
V.CURRENCY_ID,
V.CURRENCY CUR_CODE,
SUM (V.REMITTANCE_AMOUNT) AMOUNT,
V.PARTY_ID,
s.vendor_id,
s.attribute11 gl_code,
cmg_get_ccid_fnc (s.attribute11) gl_code_id,
ps.vendor_site_id,
ps.invoice_currency_code,
ps.PAYMENT_METHOD_LOOKUP_CODE
FROM CMGIN068_CHECK_REGISTERS_VW V, CMGIN.CMG_PAYEE_DETAILS_TBL P ,PO_VENDORS S ,PO_VENDOR_SITES PS
WHERE TRUNC (REMITTANCE_DATE) =P_DATE
AND P.PARTY_ID = V.PARTY_ID
AND s.vendor_id = ps.vendor_id
AND ps.pay_site_flag = 'Y'
AND ps.primary_pay_site_flag = 'Y'
AND v.to_ap_flag in ('N','E')
-- AND S.VENDOR_NAME=V.PARTY_NAME
AND s.VENDOR_TYPE_LOOKUP_CODE='INCENTIVE'
AND P.PARTY_ID=S.ATTRIBUTE4
-- AND V.PARTY_NUMBER='E'
GROUP BY V.PARTY_NAME,
TRUNC (V.REMITTANCE_DATE),
V.PARTY_NUMBER,
V.PARTY_NAME,
V.CURRENCY_ID,
V.CURRENCY,
NVL (V.TAX_AMOUNT, 0),
V.PARTY_ID,s.vendor_id,
s.attribute11 ,
cmg_get_ccid_fnc (s.attribute11),
ps.vendor_site_id,
ps.invoice_currency_code,
ps.PAYMENT_METHOD_LOOKUP_CODE
ORDER BY V.PARTY_NAME;
v_user_id NUMBER DEFAULT (fnd_global.user_id);
v_username VARCHAR2 (20);
v_email VARCHAR2 (30);
v_req_id NUMBER DEFAULT (fnd_global.conc_request_id
);
--v_dirpath VARCHAR(80);
v_outpath VARCHAR2 (80);
v_outfilename VARCHAR2 (35);
v_outfile UTL_FILE.file_type;
v_header VARCHAR2 (2500);
v_line VARCHAR2 (2500);
v_subject VARCHAR2 (100) := 'Incentive to AP E-Payment detail file';
v_record_errored NUMBER := 0;
v_record_count NUMBER := 0;
v_record_amount NUMBER := 0;
v_total_amount NUMBER := 0;
BEGIN
SELECT user_name, email_address
INTO v_username, v_email
FROM fnd_user
WHERE user_id = v_user_id;
cmg_utility_pck.write_output
( '+--------------------------------------'
|| '------------+'
);
cmg_utility_pck.write_output ('CMG Applications');
cmg_utility_pck.write_output ('Copyright (c) Comag Marketing Group');
cmg_utility_pck.write_output
( '+-------------------------------------'
|| '--------------+'
);
cmg_utility_pck.write_output ( 'Current System Time is : '
|| TO_CHAR (SYSDATE,
'DD-MON-YYYY HH24:MM:SS'
)
);
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output
( '+-------------------------------------'
|| '--------------+'
);
cmg_utility_pck.write_output ('test1'|| P_DATE);
fnd_profile.get ('CMG_OUTGOING_PATH', v_outpath);
v_outfilename :=
'RIN_Paydetails_' || TO_CHAR (SYSDATE, 'YYMMDDHHMISS')
|| '.csv';
v_outfile := UTL_FILE.fopen (v_outpath, v_outfilename, 'w');
UTL_FILE.put_line (v_outfile, ',Incentive TO AP E-Payment Details');
v_header := 'Party Number,Party Name,Error Message';
UTL_FILE.put_line (v_outfile, v_header);
FOR remit_rec IN epay_cur(P_DATE)
LOOP
IF remit_rec.amount > 0
THEN
IF remit_rec.gl_code_id IS NULL
THEN
UPDATE cmgin .cmg_remittances_tbl
SET to_ap_flag = 'E'
WHERE party_id=remit_rec.party_id
and trunc(remittance_date)=p_date;
-- cmg_utility_pck.write_output ('Remittance Id: Publisher Name: Error Message');
-- cmg_utility_pck.write_output ( remit_rec.remittance_number||' '||remit_rec.publisher_name||' '||
-- 'GL Code/Vendor Pay site id is missing please verify the Supplier Setup');
v_line :=
remit_rec.PARTY_number
|| ','
|| remit_rec.PAYEE_NAME
|| ','
|| 'GL Code is missing please verify the Supplier Setup/GL Code Combinations';
UTL_FILE.put_line (v_outfile, v_line);
v_record_errored := v_record_errored + 1;
ELSE --IF REMIT_REC.AMOUNT>0 THEN
-- INSERT AN INVOICE HEADER --
INSERT INTO ap_invoices_interface
(invoice_id,
invoice_num,
invoice_type_lookup_code, vendor_id,
vendor_site_id, invoice_amount,
invoice_currency_code, SOURCE,
GROUP_ID,
payment_method_lookup_code
)
VALUES (ap_invoices_interface_s.NEXTVAL,
remit_rec.party_number||'_'||p_date|| '_RINPAY',
--REMITTANCE_ID
'STANDARD', -- DEFAULTED
remit_rec.vendor_id,
-- PO_VENDORS.VENDOR_ID
remit_rec.vendor_site_id,
-- PO_VENDORS.VENDOR_SITE_ID
remit_rec.amount,
remit_rec.invoice_currency_code,
--PO_VENDORS.INVOICE_CURRENCY_CODE
'RIN',
-- AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'SOURCE'
'RIN_GROUP',
remit_rec.PAYMENT_METHOD_LOOKUP_CODE
);
-- INSERT INVOICE LINE --
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id, line_number,
line_type_lookup_code, amount,
dist_code_combination_id
)
VALUES (ap_invoices_interface_s.CURRVAL,
ap_invoice_lines_interface_s.NEXTVAL, 1,
'ITEM', remit_rec.amount,
remit_rec.gl_code_id
);
UPDATE cmgin .cmg_remittances_tbl
SET to_ap_flag = 'P'
WHERE party_id=remit_rec.party_id
and trunc(remittance_date)=p_date;
v_record_count := v_record_count + 1;
v_total_amount := remit_rec.amount + v_total_amount;
END IF;
END IF;
COMMIT;
END LOOP;
-- cmg_utility_pck.write_output ('Total Remittances Inserted:'||v_record_count);
-- cmg_utility_pck.write_output ('Total Amount:'||v_record_amount);
UTL_FILE.put_line(v_outfile,' ');
UTL_FILE.put_line(v_outfile,' ');
UTL_FILE.put_line(v_outfile,' ');
UTL_FILE.put_line(v_outfile,' ');
UTL_FILE.put_line (v_outfile,
'Total Remittances Inserted: ' || v_record_count
);
UTL_FILE.put_line (v_outfile, 'Total Amount: ' || v_total_amount);
UTL_FILE.put_line (v_outfile,
'Total Remittances Errored: ' || v_record_errored
);
COMMIT;
UTL_FILE.fclose (v_outfile);
IF v_email IS NULL
THEN
cmg_utility_pck.write_output('Email address is not setup for this user. '
|| 'Please contact the setup administrator'
);
ELSE
cmg_utility_pck.write_output('File created successfully');
-- email the detail file to email address for this user.
cmg_abc_submit_pck.main (v_req_id,
v_outpath || '/' || v_outfilename,
v_outfilename,
v_subject,
v_email
);
cmg_utility_pck.write_output('File emailed successfully');
END IF;
cmg_utility_pck.write_output
( '+--------------------------------------'
|| '------------+'
);
cmg_utility_pck.write_output ('Concurrent Request Completed');
cmg_utility_pck.write_output
( '+-------------------------------------'
|| '--------------+'
);
cmg_utility_pck.write_output ( 'Current System Time is : '
|| TO_CHAR (SYSDATE,
'DD-MON-YYYY HH24:MM:SS'
)
);
cmg_utility_pck.write_output (' ');
cmg_utility_pck.write_output
( '+-------------------------------------'
|| '--------------+'
);
EXCEPTION
WHEN OTHERS
THEN
error_msg := SQLCODE || ' ' || SQLERRM;
cmg_utility_pck.write_output (error_msg);
END cmg_incen_epay_to_ap_main_prc; -- end of procedure
END cmg_incen_epay_to_ap_pck; -- end of package
/
No comments:
Post a Comment