March 28, 2007, 02:06 PM
JohnBHere's the code guys. Thanks for your help.
-DEFAULT &ORDSTATUSTYPEDESC = 'OR';
-SET &FLAG = IF &ORDSTATUSTYPEDESC CONTAINS 'OR' THEN 'YES' ELSE 'NO';
-*
-DEFAULT &BEGIN_DATE_1 = 20070301;
-DEFAULT &END_DATE_1 = 20070326;
-*
-SET &BEGIN_DATE_2 = DATETRAN (DATECVT (&BEGIN_DATE_1, 'I8YYMD', 'MDYY'), '(MDYY)', '(trdD)', 'EN', 18, 'A18') ;
-SET &END_DATE_2 = DATETRAN (DATECVT (&END_DATE_1, 'I8YYMD', 'MDYY'), '(MDYY)', '(trdD)', 'EN', 18, 'A18') ;
-*
-*
-*
DEFINE FILE ABC
QUOTE_CNT/I7C=IF AIQUOTENUMBER EQ ' ' OR '0' THEN 0 ELSE 1;
PO_CNT/I7C=IF PONUMBER EQ ' ' OR '0' THEN 0 ELSE 1;
CREATEDDATE_2/MDYY = CREATEDDATE;
-*
DATEOUT_1/A16 = HCNVRT(DATEOUT, '(HYYMDI)', 16, DATEOUT_1);
DATEOUT_2/A10 =
IF EDIT (DATEOUT_1,'9999$$$$$$$$$$$$') EQ '1900'
THEN ' '
ELSE EDIT (DATEOUT_1,'$$$$$99$$$$$$$$$') | '/' |
EDIT (DATEOUT_1,'$$$$$$$$99$$$$$$') | '/' |
EDIT (DATEOUT_1,'9999$$$$$$$$$$$$');
-*
FOLLOWUPDATE_1/A16 = HCNVRT(FOLLOWUPDATE, '(HYYMDI)', 16, FOLLOWUPDATE_1);
FOLLOWUPDATE_2/A10 =
IF EDIT (FOLLOWUPDATE_1,'9999$$$$$$$$$$$$') EQ '1900'
THEN ' '
ELSE EDIT (FOLLOWUPDATE_1,'$$$$$99$$$$$$$$$') | '/' |
EDIT (FOLLOWUPDATE_1,'$$$$$$$$99$$$$$$') | '/' |
EDIT (FOLLOWUPDATE_1,'9999$$$$$$$$$$$$');
-*
AIQUOTENUMBER_2/A50 = IF AIQUOTENUMBER EQ '0' THEN ' ' ELSE AIQUOTENUMBER;
AS400AIQUOTENUMBER_2/A10 = SUBSTR(50, AS400AIQUOTENUMBER, 1, 10, 10, 'A10');
AIQUOTENUMBER_2/A50=IF AIQUOTENUMBER EQ '0' THEN ' ' ELSE AIQUOTENUMBER;
-*
COORDINATOR_LAST_NAME_1/A47=GETTOK(COORDINATORNAME, 50, 1, ',', 47, 'A47');
COORDINATOR_LAST_NAME_2/A48=TRIM('B', COORDINATOR_LAST_NAME_1, 50, ' ', 1, 'A47');
-*
COORDINATOR_FIRST_NAME_1/A47=GETTOK(COORDINATORNAME, 50, 2, ',', 47, 'A47');
COORDINATOR_FIRST_NAME_2/A48=TRIM('B', COORDINATOR_FIRST_NAME_1, 50, ' ', 1, 'A47') ;
-*
COORDINATOR_NAME_2/A196=COORDINATOR_FIRST_NAME_2 || (' ' | COORDINATOR_LAST_NAME_2);
-*
-* DOLLARVALUE_2/P14.2M = DOLLARVALUE;
-* AS400LINEITEMSUBTOTAL_2/P14.2M = AS400LINEITEMSUBTOTAL;
END
-*
-*
-*
TABLE FILE ABC
SUM
QUOTE_CNT NOPRINT AS 'Quote,Count'
PO_CNT NOPRINT AS 'PO,Count'
BY COORDINATOR_NAME_2 AS 'Coordinator,Name'
SUM
COMPUTE RECORD_COUNTER/I6 = IF CREATEDDATE_2 NE LAST CREATEDDATE_2 THEN 1 ELSE 0; NOPRINT
COMPUTE CANCELLED_RECORD_COUNTER/I6 = IF ( ORDSTATUSTYPEDESC EQ 'Cancelled' ) AND ( CREATEDDATE_2 NE LAST CREATEDDATE_2 ) THEN 1 ELSE 0; NOPRINT
AS 'CANCELLED,RECORD,COUNTER'
COMPUTE COMPLETED_RECORD_COUNTER/I6 = IF ( ORDSTATUSTYPEDESC EQ 'Complete' ) AND ( CREATEDDATE_2 NE LAST CREATEDDATE_2 ) THEN 1 ELSE 0; NOPRINT
AS 'COMPLETED,RECORD,COUNTER'
COMPUTE PENDING_RECORD_COUNTER/I6 = IF ( ORDSTATUSTYPEDESC EQ 'Pending' ) AND ( CREATEDDATE_2 NE LAST CREATEDDATE_2 ) THEN 1 ELSE 0; NOPRINT
AS 'PENDING,RECORD,COUNTER'
DATEOUT_2 NOPRINT AS 'Date,Out'
FOLLOWUPDATE_2 NOPRINT AS 'Followup,Date'
SALESREP_NAME NOPRINT AS 'Sales Rep Name'
AIQUOTENUMBER_2 NOPRINT AS 'AI,Quote,Number'
MAJORDESC NOPRINT AS 'Major,Code'
CUSTOMERNAME NOPRINT AS 'Customer Name'
CUSTOMERCONTACT NOPRINT AS 'Customer Contact'
TITLE NOPRINT AS 'Title'
COMMENTS NOPRINT AS ' Comments'
COMPUTE COUNTER/I11 = COUNTER + 1;
CNT.SPECIALORDERQUOTELINEID AS 'Actual,Item,Count'
ITEMDESCRIPTION AS 'Item,Description'
BY COORDINATOR_NAME_2 AS 'Coordinator,Name'
BY ORDSTATUSTYPEDESC AS 'Status'
BY CREATEDDATE_2 AS 'Created,Date'
BY SALESREP_NAME AS 'Sales Rep Name'
BY AIQUOTENUMBER_2 AS 'AI,Quote,Number'
BY CUSTOMERNAME AS 'Customer Name'
BY MAJORDESC AS 'Major,Code'
BY CUSTOMERCONTACT AS 'Customer,Contact'
BY TITLE AS 'Title'
BY COMMENTS AS 'Comments'
BY ITEMCOUNT AS 'Quote,Item,Count'
BY PONUMBER AS 'PO #'
PRINT
DOLLARVALUE/P14.2M AS 'Dollar,Value'
QUANTITY/I7 AS 'Quantity'
MARGIN/P7.2M NOPRINT AS 'Margin'
ISPURCHASED AS 'Items,Purchased'
AS400AIQUOTENUMBER AS 'AS400,AI,QUOTE,NUMBER'
AS400ITEMNAME AS 'AS400,ITEM,NAME'
AS400ORDERNUMBER AS 'AS400,ORDER,NUMBER'
AS400PONUMBER AS 'AS400,PO,NUMBER'
AS400LINEITEMSUBTOTAL/P14.2M AS 'AS400,DOLLAR,VALUE'
QUOTE_CNT AS 'Quote,Count'
PO_CNT AS 'PO,Count'
ITEMCOUNT NOPRINT AS 'Quote,Item,Count'
BY COORDINATOR_NAME_2 AS 'Coordinator,Name'
BY ORDSTATUSTYPEDESC AS 'Status'
BY CREATEDDATE_2 AS 'Created,Date'
BY SALESREP_NAME AS 'Sales Rep Name'
BY AIQUOTENUMBER_2 AS 'AI,Quote,Number'
BY CUSTOMERNAME AS 'Customer Name'
BY MAJORDESC AS 'Major,Code'
BY CUSTOMERCONTACT AS 'Customer,Contact'
BY TITLE AS 'Title'
BY COMMENTS AS 'Comments'
BY ITEMCOUNT AS 'Quote,Item,Count'
BY PONUMBER AS 'PO #'
ON COORDINATOR_NAME_2 SUBFOOT
"Totals for <COORDINATOR_NAME_2 "
"Pending count: <ST.PENDING_RECORD_COUNTER "
"Cancelled count: <ST.CANCELLED_RECORD_COUNTER "
"Completed count: <ST.COMPLETED_RECORD_COUNTER "
" "
WHEN '&FLAG' EQ 'YES';
ON ORDSTATUSTYPEDESC SUBTOTAL
ITEMCOUNT
SPECIALORDERQUOTELINEID
DOLLARVALUE
QUANTITY
ISPURCHASED
AS400ORDERNUMBER
AS400LINEITEMSUBTOTAL AS 'Total '
ON ORDSTATUSTYPEDESC SUBFOOT
"Total count of <ORDSTATUSTYPEDESC records for <COORDINATOR_NAME_2 <ST.RECORD_COUNTER "
" "
" "
HEADING
"Special Order Quote Log"
"From &BEGIN_DATE_2 to &END_DATE_2"
"For <COORDINATOR_NAME_2 "
"Run Date: <+0>&DATEtMDYY <+0> "
" "
WHERE CREATEDDATE FROM &BEGIN_DATE_1 TO &END_DATE_1;
WHERE COORDINATORNAME EQ &COORDINATORNAME.(OR(FIND COORDINATORNAME IN SPECIALORDER_DATA)).COORDINATORNAME.;
WHERE ORDSTATUSTYPEDESC EQ &ORDSTATUSTYPEDESC.(OR(<Cancelled,Cancelled>,<Complete,Complete>,<Pending,Pending>)).ORDSTATUSTYPEDESC.;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K FORMULA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
LINES-PER-PAGE=20,
$
TYPE=DATA,
COLUMN=N30,
COLOR='BLACK',
STYLE=NORMAL,
$
TYPE=DATA,
COLUMN=N43,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N30,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N43,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N14,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N13,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N12,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N8,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N9,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N10,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N11,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N6,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N7,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N28,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N32,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N3,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N4,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N5,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N21,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N23,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N25,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N26,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N27,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N29,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N31,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N15,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N16,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N17,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N18,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N19,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N20,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N34,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N35,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N36,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N38,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N37,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N39,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N1,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N2,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N22,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N24,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N44,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N33,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N45,
STYLE=BOLD,
$
TYPE=TITLE,
COLUMN=N42,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N40,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=TITLE,
COLUMN=N41,
STYLE=BOLD,
JUSTIFY=CENTER,
$
TYPE=SUBHEAD,
BY=2,
LINE=2,
OBJECT=TEXT,
ITEM=1,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
BY=2,
LINE=2,
OBJECT=FIELD,
ITEM=1,
SIZE=12,
STYLE=BOLD,
$
TYPE=REPORT,
COLUMN=N28,
SEQUENCE=25,
$
TYPE=REPORT,
COLUMN=N30,
WRAP=1.500000,
SEQUENCE=15,
$
TYPE=REPORT,
COLUMN=N43,
WRAP=0.750000,
$
TYPE=REPORT,
COLUMN=N14,
SQUEEZE=1.430556,
SEQUENCE=17,
$
TYPE=REPORT,
COLUMN=N13,
SQUEEZE=1.180556,
SEQUENCE=13,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=1.777778,
SEQUENCE=12,
$
TYPE=REPORT,
COLUMN=N8,
WRAP=2.777778,
SEQUENCE=9,
$
TYPE=REPORT,
COLUMN=N9,
SEQUENCE=8,
$
TYPE=REPORT,
COLUMN=N10,
WRAP=1.666667,
SEQUENCE=10,
$
TYPE=REPORT,
COLUMN=N11,
WRAP=1.402778,
SEQUENCE=11,
$
TYPE=REPORT,
COLUMN=N6,
SEQUENCE=6,
$
TYPE=REPORT,
COLUMN=N7,
SQUEEZE=1.402778,
SEQUENCE=7,
$
TYPE=REPORT,
COLUMN=N32,
SEQUENCE=16,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=0.680556,
SEQUENCE=4,
$
TYPE=REPORT,
COLUMN=N5,
WRAP=0.680556,
SEQUENCE=5,
$
TYPE=REPORT,
COLUMN=N21,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N25,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N26,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N27,
WRAP=3.500000,
$
TYPE=REPORT,
COLUMN=N29,
SEQUENCE=14,
$
TYPE=REPORT,
COLUMN=N31,
WRAP=1.500000,
SEQUENCE=19,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N16,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N17,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N18,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N19,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N20,
WRAP=0.680556,
$
TYPE=REPORT,
COLUMN=N34,
WRAP=0.750000,
SEQUENCE=18,
$
TYPE=REPORT,
COLUMN=N35,
WRAP=1.000000,
SEQUENCE=20,
$
TYPE=REPORT,
COLUMN=N36,
WRAP=1.000000,
SEQUENCE=21,
$
TYPE=REPORT,
COLUMN=N38,
WRAP=1.000000,
SEQUENCE=23,
$
TYPE=REPORT,
COLUMN=N37,
WRAP=1.000000,
SEQUENCE=22,
$
TYPE=REPORT,
COLUMN=N39,
WRAP=1.000000,
SEQUENCE=24,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=1.250000,
SEQUENCE=1,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=0.750000,
$
TYPE=REPORT,
COLUMN=N22,
WRAP=1.000000,
$
TYPE=REPORT,
COLUMN=N24,
WRAP=1.500000,
$
TYPE=REPORT,
COLUMN=N44,
WRAP=0.750000,
$
TYPE=REPORT,
COLUMN=N40,
SEQUENCE=2,
$
TYPE=REPORT,
COLUMN=N41,
SEQUENCE=3,
$
ENDSTYLE
END