Oracle Apps Technical Blog: Package to Create the report in html format

create or replace PACKAGE XXABS_RCPT_LIST_REP_PKG
AS
/*###########################################################################################################
PROCEDURE NAME : XXABS_RCPT_LIST

CREATION DATE : April 11, 2012

PARAMETERS
===========
PARAMETER NAME TYPE DATA TYPE DEFAULT REMARKS
----------------------- --------- ------------------- -------- -----------------------------------------
retcode out VARCHAR2
retbuff out NUMBER
-----------------------------------------------------------------------------------------------------------
DISCRIPTION : Prodedure to generate the List Of Receipts in html format. This report
can be saved in .xls format to get it in excel sheet.

VERSIONING:
===========
VRESION DATE AUTHOR REMARKS
------------ ----------------- ------------------------------ ------------------------------------------
1.0 April 11, 2012 Ashok Goud Initial Version
-----------------------------------------------------------------------------------------------------------

############################################################################################################*/

PROCEDURE XXABS_RCPT_LIST(retbuff OUT VARCHAR2
, retcode OUT NUMBER
, P_WAREHOUSE IN NUMBER
--, P_TO_WH IN NUMBER
, P_PERIOD_FROM IN DATE
, P_PERIOD_TO IN DATE
, P_TRANS_TYPE IN VARCHAR2
);
END XXABS_RCPT_LIST_REP_PKG;

create or replace PACKAGE BODY XXABS_RCPT_LIST_REP_PKG

PROCEDURE XXABS_RCPT_LIST(retbuff OUT VARCHAR2

, retcode OUT NUMBER

, P_WAREHOUSE IN NUMBER

-- , P_TO_WH IN NUMBER

, P_PERIOD_FROM IN DATE

, P_PERIOD_TO IN DATE

, P_TRANS_TYPE IN VARCHAR2

)

/*###########################################################################################################

PROCEDURE NAME : XXABS_RCPT_LIST

CREATION DATE : April 11, 2012

PARAMETERS

===========

PARAMETER NAME TYPE DATA TYPE DEFAULT REMARKS

----------------------- --------- ------------------- -------- -----------------------------------------

retcode out VARCHAR2

retbuff out NUMBER

-----------------------------------------------------------------------------------------------------------

DISCRIPTION : Prodedure to generate the List Of Receipts in html format. This report

can be saved in .xls format to get it in excel sheet.

VERSIONING:

===========

VRESION DATE AUTHOR REMARKS

------------ ----------------- ------------------------------ ------------------------------------------

1.0 April 11, 2012 Ashok Goud Initial Version

-----------------------------------------------------------------------------------------------------------

############################################################################################################*/

-------------------- cursor to find the List Of Receipts wrt. parameters provided ------

CURSOR c_receipt_list

SELECT NULL REC_SNO

, NULL REC_STATUS

, MSIB.SEGMENT1||MSIB.SEGMENT2 SIT_ITEM_NO

, RSL.QUANTITY_RECEIVED SBA_ORIG_QTY

, TO_CHAR(RCT.TRANSACTION_DATE,'dd/mm/yyyy hh24:mi:ss') RECD_DATE

, RCT.TRANSACTION_DATE

, NULL REC_CRE_DT

, NULL SBA_EXP_QTY

, NULL SBA_BAT_STAT

, RSH.RECEIPT_NUM RECEIPT_NUM

, RCT.PO_UNIT_PRICE SIT_MKT_VAL

, NULL ISH_NO

, NULL ISH_SNO

, OOD.ORGANIZATION_NAME SRC_P_WH

, OOD1.ORGANIZATION_NAME DEST_V_WH

, RCT.TRANSACTION_TYPE MOT_CODE

, OAP.PERIOD_NAME ACNT_PERIOD_DT

, NULL POST_STATUS

, NULL REC_SOURCE_DESC

, NULL GL_EXCLUDED

, NULL TOTAL_VALUE

, RSH.SHIPMENT_NUM SHIPMENT_NUMBER

, RSH.RECEIPT_SOURCE_CODE RECEIPT_SOURCE_CODE

, RSL.REQUISITION_LINE_ID REQUISITION_LINE_ID

, RSL.REQ_DISTRIBUTION_ID REQ_DISTRIBUTION_ID

, MSIB.ORGANIZATION_ID

, MSIB.INVENTORY_ITEM_ID

, MMT.TRANSACTION_QUANTITY

FROM RCV_SHIPMENT_HEADERS RSH

, RCV_SHIPMENT_LINES RSL

, MTL_MATERIAL_TRANSACTIONS MMT

, MTL_SYSTEM_ITEMS_B MSIB

, RCV_TRANSACTIONS RCT

, ORG_ORGANIZATION_DEFINITIONS OOD

, ORG_ORGANIZATION_DEFINITIONS OOD1

, ORG_ACCT_PERIODS OAP

WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

AND MMT.SHIPMENT_NUMBER = RSH.SHIPMENT_NUM

--AND MMT.TRANSFER_ORGANIZATION_ID = RSH.SHIP_TO_ORG_ID

AND MMT.TRANSACTION_ID = RSL.MMT_TRANSACTION_ID

AND MMT.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID

AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID

AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID

AND RSL.FROM_ORGANIZATION_ID = OOD.ORGANIZATION_ID

AND RSL.TO_ORGANIZATION_ID = OOD1.ORGANIZATION_ID

AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

AND RSL.ITEM_ID = MSIB.INVENTORY_ITEM_ID

AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID

--AND MMT.TRANSACTION_ID(+) = RCT.TRANSACTION_ID

--AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY'

AND RCT.TRANSACTION_TYPE = 'RECEIVE'

AND RCT.ORGANIZATION_ID = OOD1.ORGANIZATION_ID

AND OAP.ORGANIZATION_ID = OOD.ORGANIZATION_ID

AND TRUNC(SYSDATE) BETWEEN TRUNC(OAP.PERIOD_START_DATE) AND TRUNC(OAP.SCHEDULE_CLOSE_DATE)

-- AND MSIB.ORGANIZATION_ID=594

AND RCT.DESTINATION_TYPE_CODE='RECEIVING'

AND TO_DATE(RCT.TRANSACTION_DATE,'DD-MON-YY') BETWEEN NVL(P_PERIOD_FROM,RCT.TRANSACTION_DATE) AND NVL(P_PERIOD_TO,NVL(P_PERIOD_FROM,RCT.TRANSACTION_DATE))

AND MSIB.ORGANIZATION_ID =NVL(P_WAREHOUSE,MSIB.ORGANIZATION_ID)

AND RCT.TRANSACTION_TYPE =NVL(P_TRANS_TYPE,RCT.TRANSACTION_TYPE);

BEGIN

FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_WAREHOUSE1 '||P_WAREHOUSE);

FND_FILE.PUT_LINE(FND_FILE.LOG, 'XXABS_RCPT_LIST_REPORT initiated at '||TO_CHAR(SYSDATE,'dd-MON-YYYY HH24:MI:SS'));

------------------------------------- HTML FORMAT -------------------------------------

FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<html>

<tr bgcolor= "#B0E0E6"><td colspan=8 style="font-size: 30px; text-align: center">ABS Oracle Intransit Report</td></tr>

<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px">Parameters:

<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px">WAREHOUSE:

<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px">WAREHOUSE NAME:

<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px">TRANSACTION TYPE:

<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px">PERIOD FROM:

<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px">PERIOD TO:

</td>'||'

<td colspan=2 style="text-align=top">Report generated on '||TRUNC(sysdate)||'</td></tr>'

------------------------------------- Table Header -------------------------------------

||' <tr style="font-size: 13px; background-color: #B0E0E6">

<td width="50px">REC_STATUS</td>

<td width="50px">RECEIPT_NUM</td>

<td width="70px">ACNT_PERIOD_DT</td>

<td width="70px">POST_STATUS</td>

<td width="70px">REC_SOURCE_DESC</td>

<td width="70px">GL_EXCLUDED</td>

<td width="70px">TOTAL_VALUE</td>

</tr>');

FND_FILE.PUT_LINE(FND_FILE.LOG,'1234');

FOR i IN c_receipt_list

LOOP

-- FND_FILE.PUT_LINE(FND_FILE.LOG,'loop starting');

------------------------------------- Output Data to table -------------------------------------

FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr style="font-size: 11px; text-align: left">

<td>'||NVL(i.REC_STATUS,'--')||'</td>

<td>'||i.RECEIPT_NUM||'</td>

<td>'||NVL(i.ACNT_PERIOD_DT,'--')||'</td>

<td>'||NVL(i.POST_STATUS,'--')||'</td>

<td>'||NVL(i.REC_SOURCE_DESC,'--')||'</td>

<td>'||NVL(i.GL_EXCLUDED,'--')||'</td>

<td>'||NVL(i.TOTAL_VALUE,'--')||'</td>

</td></tr>');

-- FND_FILE.PUT_LINE(FND_FILE.LOG,'loop terminating');

END LOOP;

FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>

</body>

</html>');

FND_FILE.PUT_LINE(FND_FILE.LOG,'XXABS_RCPT_LIST successfully executed at '||TO_CHAR(SYSDATE,'dd-MON-YYYY HH24:MI:SS'));

EXCEPTION

WHEN OTHERS

THEN

retbuff := 'XXABS_RCPT_LIST execution terminated at '||TO_CHAR(SYSDATE,'dd-MON-YYYY HH24:MI:SS')||' ERROR# '||SQLCODE||' | ERROR MSG: '||SQLERRM;

retcode := 2;

FND_FILE.PUT_LINE(FND_FILE.LOG, 'XXABS_RCPT_LIST execution terminated at '||TO_CHAR(SYSDATE,'dd-MON-YYYY HH24:MI:SS')||' ERROR# '||SQLCODE||' | ERROR MSG: '||SQLERRM);

END XXABS_RCPT_LIST;

END XXABS_RCPT_LIST_REP_PKG;

Oracle Apps Technical Blog

Total Pageviews

Wednesday, 16 October 2013

Package to Create the report in html format

1 comment:

Blog Archive