CREATE OR REPLACE package body APPS.cmgin097_pck AS
Procedure cmgin097_prc(errbuf out varchar2,
retcode out varchar2,
p_date date) AS
CURSOR cmg_remit_cur IS
SELECT
distinct crt.remittance_id rem_id,
nvl(cp.rda_rdp_account_number,' ') party_number,
substr(cp.industry_standard_number,1,7) legacy
FROM
cmg_parties_tbl cp,
cmg_remittances_tbl crt,
cmg_incentive_lookups_tbl clt
WHERE
cp.party_id = crt.party_id
and crt.check_number IS NULL
and crt.status_id = clt.incentive_lookup_id
and clt.incentive_lookup_type = 'remittance_status'
--and clt.code = 'appr';
and clt.code ='chk_gen'
and trunc(remittance_date)= p_date;
CURSOR cmg_hdr_cur(p_remit_id IN NUMBER) IS
SELECT
distinct claim_header_id voucher
FROM
cmg_claim_lines_tbl
WHERE
remittance_id = p_remit_id;
CURSOR cmg_line_cur(p_hdr_id IN NUMBER,
p_remit_id IN NUMBER) IS
SELECT
cph.product_number bipad,
substr(cph.title,1,14) title,
substr(cpl.issue_code,3,4) issue,
cpl.cover_date cover_date,
cp.calculation_id,
-- decode(cp.program_based_on,'pockets',ccl.number_of_pockets,
-- round(decode(cil.code, 'PII', draw_copies, 'NCD', draw_copies, sale_copies*NVL(map,100)/100))) sale,
-- round(ccl.amount/(decode(cp.program_based_on,'copies',
-- round(decode(cil.code, 'PII', draw_copies, 'NCD', draw_copies, sale_copies*NVL(map,100)/100)),
-- ccl.number_of_pockets)),5) amount,
decode(cp.program_based_on,'pockets',ccl.number_of_pockets,
round(decode(cp.calculation_id,680, draw_copies, sale_copies*NVL(map,100)/100))) sale,
round(ccl.amount/(decode(cp.program_based_on,'copies',
round(decode(cp.calculation_id,680, draw_copies, sale_copies*NVL(map,100)/100)),
ccl.number_of_pockets)),5) amount,
cil.code trcode,
decode(cil1.code,'est', decode(ccl.audit_adjustment_flag,'Y','AU','EP'),
'estrev',decode(ccl.audit_adjustment_flag,'Y','AU','ER'),
'estact',decode(ccl.audit_adjustment_flag,'Y','AU','RP'),
'adj',decode(pg.code, 'IMPACT', 'ADJ','adj'),
'actrev',decode(ccl.audit_adjustment_flag,'Y','AU','RR'),
'act',decode(pg.code, 'IMPACT', 'ACT', decode(ccl.audit_adjustment_flag,'Y','AU','RP')),
'RP') trxcode,-- SCR 8075 RDA AUDIT
-- decode(cil1.code,'est', 'EP','estrev','ER','estact', 'RP',
-- 'adj',decode(pg.code, 'IMPACT', 'ADJ','adj'),'actrev','RR',
-- 'act',decode(pg.code, 'IMPACT', 'ACT', 'RP'),'RP') trxcode,
ccl.claim_line_id
FROM
cmg_program_groups_tbl pg,
cmg_agreement_lines_tbl cal,
cmg_agreement_templates_tbl cat,
cmg_program_headers_tbl cp,
cmg_authorized_issues_tbl cai,
cmg_product_headers_tbl cph,
cmg_product_lines_tbl cpl,
cmg_claim_headers_tbl cch,
cmg_claim_lines_tbl ccl,
cmg_incentive_lookups_tbl cil,
cmg_incentive_lookups_tbl cil1
WHERE
pg.program_group_id = cp.program_group_id
and cil1.incentive_lookup_type = 'transaction_codes'
and cil1.incentive_lookup_id = ccl.transaction_code_id
and cil.incentive_lookup_type = 'credit_memo'
and cil.incentive_lookup_id = cp.credit_memo_id
and cp.program_header_id = cat.program_header_id
and cat.agreement_template_id = cal.agreement_template_id
and cal.agreement_line_id = ccl.agreement_line_id
and cph.product_header_id = cpl.product_header_id
and cpl.product_line_id = cai.product_line_id
and cai.authorized_issue_id = ccl.authorized_issue_id
and ccl.amount != 0
and ccl.remittance_id = p_remit_id
and ccl.claim_header_id = cch.claim_header_id
and cch.remittance_flag = 'Y'
and cch.claim_header_id = p_hdr_id
and cp.payment_type ='credits';
/* GROUP BY
cph.product_number,
cph.title,
cpl.issue_code,
cpl.on_sale_date,
cpl.cover_date,
ccl.amount,
cil.code;
*/
V_FILEHANDLE1 UTL_FILE.FILE_TYPE;
V_FILEHANDLE2 UTL_FILE.FILE_TYPE;
v_file VARCHAR2(30);
v_file_roar VARCHAR2(250);
v_path1 VARCHAR2(100);
v_path2 VARCHAR2(100);
v_header number;
v_pgcode varchar2(10);
LINE1 VARCHAR2(500);
v_logfile VARCHAR2(25);
v_req_id NUMBER DEFAULT(FND_GLOBAL.CONC_REQUEST_ID);
v_request_id NUMBER;
v_userid NUMBER DEFAULT(FND_GLOBAL.USER_ID);
v_username VARCHAR2(20);
v_file_amt number:=0;
v_trcode VARCHAR2(10);
v_po_number VARCHAR2(20);
v_subject VARCHAR2(100);
v_report VARCHAR2(100);
v_email_address VARCHAR2(100);
BEGIN
SELECT user_name,email_address
INTO v_username,v_email_address
FROM fnd_user
WHERE user_id = v_userid;
FND_PROFILE.GET('CMG_INCEN_OUTPUT_TO_ROAR_PATH',v_path1);
FND_PROFILE.GET('CMG_OUTPUTFILE_PATH',v_path2);
v_file := 'INCENCR'||to_char(sysdate,'YYYYMMDDHH24MISS')||'.DAT';
v_logfile := 'o'||to_char(v_req_id)||'.out'; -- Log file
V_FILEHANDLE1 := UTL_FILE.FOPEN(v_path1,v_file,'w');
V_FILEHANDLE2 := UTL_FILE.FOPEN(v_path2,v_logfile,'w');
UTL_FILE.PUT_LINE(V_FILEHANDLE2,'-----------------'
||'CMG Incentive Credits to Roar ---------------');
UTL_FILE.PUT_LINE(V_FILEHANDLE2,' ');
UTL_FILE.PUT_LINE(V_FILEHANDLE2,'Current System Time is : '
||TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'));
UTL_FILE.PUT_LINE(V_FILEHANDLE2,' ');
UTL_FILE.PUT_LINE(V_FILEHANDLE2,' Incentive Credits File Name is '||v_file);
UTL_FILE.PUT_LINE(V_FILEHANDLE2,' ');
/* UTL_FILE.PUT_LINE(V_FILEHANDLE2,'Legacy# Voucher Bipad Title '
||'Issue Amount'); */
-- UTL_FILE.PUT_LINE(V_FILEHANDLE2,'date'||p_date);
FOR rmt in cmg_remit_cur LOOP -- Start Remittance Loop
FOR hdr in cmg_hdr_cur(rmt.rem_id) LOOP -- Header Loop
FOR lin in cmg_line_cur (hdr.voucher,rmt.rem_id) LOOP -- Line Loop
-- if lin.trcode in ('PII', 'NCD') then
-- v_trcode := lin.trcode;
-- else
-- v_trcode := lin.trcode||lin.trxcode;
-- end if;
-- modified as part of SCR 8339
if lin.calculation_id =680 then
v_trcode := lin.trcode;
else
v_trcode := lin.trcode||lin.trxcode;
end if;
-- if lin.trxcode='AUD' then
-- v_po_number := rmt.party_number||hdr.voucher||'AUD';
-- else
v_po_number :=rmt.party_number||hdr.voucher;
-- end if;
LINE1 := rmt.legacy ||','||
v_trcode ||','||
v_po_number ||','||
lin.bipad ||','||
lin.issue ||','||
-1*lin.sale ||','||
lin.amount ||',"'||
lin.title ||'",'||
lin.cover_date||','||
lin.claim_line_id;
UTL_FILE.PUT_LINE(V_FILEHANDLE1,LINE1);
/* UTL_FILE.PUT_LINE(V_FILEHANDLE2,rpad(rmt.legacy,10,' ')
||rpad(hdr.voucher,10,' ')
||rpad(lin.bipad,7,' ')
||rpad(lin.title,18,' ')
||rpad(lin.issue,10,' ')
||to_char(lin.amount,'9999999.99')); */
v_file_amt := v_file_amt + round(lin.amount*lin.sale,2);
-- Update Remittance Table
UPDATE cmg_remittances_tbl
SET status_id = (select incentive_lookup_id
from cmg_incentive_lookups_tbl
where code = 'cr_procd'
and incentive_lookup_type = 'remittance_status')
WHERE remittance_id = rmt.rem_id;
END LOOP; -- Line Loop
END LOOP; --Header Loop
-- Update Remittance Table
-- UPDATE cmg_remittances_tbl
-- SET status_id = (select incentive_lookup_id
-- from cmg_incentive_lookups_tbl
-- where code = 'cr_procd'
-- and incentive_lookup_type = 'remittance_status')
-- WHERE remittance_id = rmt.rem_id;
END LOOP; -- Remittance Loop
UTL_FILE.PUT_LINE(V_FILEHANDLE2,' Total Credit Amount is '||(v_file_amt*-1));
UTL_FILE.fclose(V_FILEHANDLE1); -- Close the Credit File
UTL_FILE.fclose(V_FILEHANDLE2); -- Close Log File
v_subject := 'Roar Interface Credit Memo Flat File';
IF v_email_address IS NOT NULL THEN
v_request_id := Fnd_Request.SUBMIT_REQUEST(
'CMGABC',
'CMGABC_EMAIL',
'Send E Mail from Server',
'',
FALSE,
v_path1,
v_file,
v_subject,
v_email_address );
COMMIT;
ELSE
UTL_FILE.PUT_LINE(V_FILEHANDLE2,' Could not Find Email Address in the User setup ,Please Contact User Setup' );
END IF;
end cmgin097_prc;
end cmgin097_pck;
/
No comments:
Post a Comment