Environment: Oracle database, HPUX
Description: This script was run nightly via CRON to summarize Accounts Receivable information from JIT ERP database.
Code Listing:
rem $Author: roblaw $
rem $Locker: roblaw $
rem $Date: 1996/05/24 22:22:44 $
rem $Source: /appl/jit/RCS/ar/src/aropu.sql,v $
rem $Revision: 1.1 $
/* program aropu.sql
date 05-16-96
author robert lawson
purpose: create open AR (accounts receivable) files.
Notes:
1. How to know create date of table?
*/
-- set echo on
set termout on
set verify on
accept i_customer prompt "Customer (! = all): "
accept i_date prompt "Age Date MM/DD/YY (= sysdate): "
set termout off
declare
/*
===============================================================
Variable declared
===============================================================
*/
o_CUST_NO arinvsum.cust_no%type;
o_INVNO arinvsum.invno%type;
o_INVOICE_TYPE arinvsum.invoice_type%type;
o_ORIG_AMT arinvsum.orig_amt%type;
open_balance arinvsum.orig_amt%type;
days_current arinvsum.orig_amt%type;
days_01_30 arinvsum.orig_amt%type;
days_31_60 arinvsum.orig_amt%type;
days_61_90 arinvsum.orig_amt%type;
days_91pls arinvsum.orig_amt%type;
o_PLANT arinvsum.plant%type;
o_ORDER_NO arinvsum.order_no%type;
o_ORDER_SUFFIX arinvsum.order_suffix%type;
o_REFERENCE_ID arinvsum.reference_id%type;
o_INVDATE arinvsum.invdate%type;
o_INV_DUE_DATE arinvsum.inv_due_date%type;
o_DATE_LAST_APPLIED arinvsum.date_last_applied%type;
o_TERMS_CD arinvsum.terms_cd%type;
o_TERMS_DESC arinvsum.terms_desc%type;
o_SALES_ID1 oehead.sales_id1%type;
o_CUST_NAME oehead.cust_name%type;
o_CUST_CITY oehead.cust_city%type;
o_CUST_STATE oehead.cust_state%type;
o_CUST_ZIP_CODE oehead.cust_zip_code%type;
o_COUNTRY oehead.country%type;
o_sls_name slsmaster.sls_name%type;
segment varchar2(5); /* market segment */
club varchar2(5);/* retail club level */
bucket numeric(2); /* 0=cur, 1=30, 2=60, 3=90, 4=90+ */
b_date date; /* bucket date */
o_open_balance ar_summary.open_balance%type;
o_days_current ar_summary.days_current%type;
o_days_01_30 ar_summary.days_01_30%type;
o_days_31_60 ar_summary.days_31_60%type;
o_days_61_90 ar_summary.days_61_90%type;
o_days_91pls ar_summary.days_91pls%type;
l_count numeric(10,5) := 0; /* commit counter */
/*
===============================================================
Cursors
===============================================================
*/
cursor get_arinvsum is
SELECT
ar.cust_no,
ar.invno,
ar.invoice_type,
nvl(ar.orig_amt,0),
(nvl(ar.orig_amt,0) + nvl(ar.totalpay,0) + nvl(ar.otheramt,0) +
nvl(ar.amt_xfer,0) + nvl(ar.adjustments,0) + nvl(ar.disc_taken,0)),
ar.plant,
ar.order_no,
ar.order_suffix,
ar.reference_id,
ar.invdate,
ar.inv_due_date,
ar.date_last_applied,
ar.terms_cd,
ar.terms_desc,
oh.sales_id1,
oh.cust_name,
oh.cust_city,
oh.cust_state,
oh.cust_zip_code,
oh.country,
sm.sls_name,
decode(cm.family_cd,'CONT','CONT','DESN','DESN','RETL'),
decode(cm.family_cd,'CONT',null,'DESN',null,'RETL',null,cm.family_cd)
FROM
slsmaster sm, cm_accts cm, oehead oh, arinvsum ar
WHERE
('&i_customer' = '!' or ar.cust_no = upper('&i_customer')) and
(nvl(ar.orig_amt,0) + nvl(ar.totalpay,0) + nvl(ar.otheramt,0) +
nvl(ar.amt_xfer,0) + nvl(ar.adjustments,0) + nvl(ar.disc_taken,0) <> 0) and
oh.invno(+) = ar.invno and
cm.cust_no(+) = ar.cust_no and
sm.slsman(+) = oh.sales_id1;
cursor get_ar is
SELECT
cust_no,
sum(open_balance),
sum(days_current),
sum(days_01_30),
sum(days_31_60),
sum(days_61_90),
sum(days_91pls)
FROM ar_header
GROUP BY cust_no;
/*
===============================================================
In the beginning
===============================================================
*/
begin
-- truncate table ar_header;
delete from ar_header;
commit;
-- truncate table ar_summary;
delete from ar_summary;
commit;
/*
===============================================================
Get header
===============================================================
*/
open get_arinvsum;
loop
fetch get_arinvsum into
o_cust_no, o_invno, o_invoice_type, o_orig_amt, open_balance, o_plant,
o_order_no, o_order_suffix, o_reference_id, o_invdate, o_inv_due_date, o_date_last_applied,
o_terms_cd, o_terms_desc, o_sales_id1, o_cust_name, o_cust_city, o_cust_state,
o_cust_zip_code, o_country, o_sls_name, segment, club ;
exit when get_arinvsum%notfound;
days_current := 0; days_01_30 := 0; days_31_60 := 0; days_61_90 := 0; days_91pls := 0;
if '&i_date' is null then
b_date := sysdate;
else
b_date := to_date('&i_date','MM/DD/YY');
end if;
bucket := least(4,ceil(greatest(0,(b_date - o_INV_DUE_DATE))/30));
if bucket = 0 then
days_current := days_current + open_balance;
elsif bucket = 1 then
days_01_30 := days_01_30 + open_balance;
elsif bucket = 2 then
days_31_60 := days_31_60 + open_balance;
elsif bucket = 3 then
days_61_90 := days_61_90 + open_balance;
else /* 4 */
days_91pls := days_91pls + open_balance;
end if;
/*
===============================================================
Insert rows
===============================================================
*/
INSERT INTO ar_header
(CUST_NO, INVNO, INVOICE_TYPE, ORIG_AMT, open_balance, days_current,
days_01_30, days_31_60, days_61_90, days_91pls, PLANT,
ORDER_NO, ORDER_SUFFIX, REFERENCE_ID, INVDATE, INV_DUE_DATE,
DATE_LAST_APPLIED, TERMS_CD, TERMS_DESC, SALES_ID1,
CUST_NAME, CUST_CITY, CUST_STATE, CUST_ZIP_CODE ,COUNTRY,
SLS_NAME, segment, club )
VALUES
(o_CUST_NO, o_INVNO, o_INVOICE_TYPE, o_ORIG_AMT, open_balance, days_current,
days_01_30, days_31_60, days_61_90, days_91pls, o_PLANT,
o_ORDER_NO, o_ORDER_SUFFIX, o_REFERENCE_ID, o_INVDATE, o_INV_DUE_DATE,
o_DATE_LAST_APPLIED, o_TERMS_CD, o_TERMS_DESC, o_SALES_ID1,
o_CUST_NAME, o_CUST_CITY, o_CUST_STATE, o_CUST_ZIP_CODE, o_COUNTRY,
o_SLS_NAME, segment, club);
l_count := l_count + 1;
if mod(l_count,20) = 0 then
commit;
l_count := 0;
end if;
end loop; /* arinvsum */
close get_arinvsum;
commit;
/*
===============================================================
Summary tables
===============================================================
*/
open get_ar;
loop
fetch get_ar into
o_cust_no, o_open_balance, o_days_current, o_days_01_30, o_days_31_60,
o_days_61_90, o_days_91pls;
exit when get_ar%notfound;
INSERT INTO ar_summary
(cust_no, open_balance, days_current, days_01_30, days_31_60, days_61_90, days_91pls)
VALUES
(o_cust_no, o_open_balance, o_days_current, o_days_01_30, o_days_31_60,
o_days_61_90, o_days_91pls);
end loop; /* ar */
close get_ar;
/*
===============================================================
El Fin
===============================================================
*/
commit;
end;
/
exit
No comments:
Post a Comment