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;
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
AS
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
)
IS
/*###########################################################################################################
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
IS
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>
<body style="font-family:calibri; color:#000000">
<table border =1 bordercolor= "#B0E0E6">
<tr bgcolor= "#B0E0E6"><td colspan=8 style="font-size: 30px; text-align: center"><b><u>ABS Oracle Intransit Report</u></b></td></tr>
<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px"><u>Parameters:</u><br/>
<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px"><u>WAREHOUSE:</u><br/>
<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px"><u>WAREHOUSE NAME:</u><br/>
<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px"><u>TRANSACTION TYPE:</u><br/>
<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px"><u>PERIOD FROM:</u><br/>
<tr bgcolor= "#F0FFFF"><td colspan=6 align=left style="font-size: 12px"><u>PERIOD TO:</u><br/>
</td>'||'
<td colspan=2 style="text-align=top">Report generated on </br>'||TRUNC(sysdate)||'</td></tr>'
------------------------------------- Table Header -------------------------------------
||' <tr style="font-size: 13px; background-color: #B0E0E6">
<td width ="50px">REC_SNO</td>
<td width="50px">REC_STATUS</td>
<td width="400px">SIT_ITEM_NO</td>
<td width="50px">SBA_ORIG_QTY</td>
<td width="50px">RECD_DATE</td>
<td width="70px">REC_CRE_DT</td>
<td width="50px">SBA_EXP_QTY</td>
<td width="70px">SBA_BAT_STAT</td>
<td width="50px">RECEIPT_NUM</td>
<td width="70px">SIT_MKT_VAL</td>
<td width="50px">ISH_NO</td>
<td width="50px">ISH_SNO</td>
<td width="70px">SRC_P_WH</td>
<td width="70px">DEST_V_WH</td>
<td width="70px">MOT_CODE</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_SNO,'--')||'</td>
<td>'||NVL(i.REC_STATUS,'--')||'</td>
<td>'||i.SIT_ITEM_NO||'</td>
<td>'||i.SBA_ORIG_QTY||'</td>
<td>'||i.RECD_DATE||'</td>
<td>'||i.REC_CRE_DT||'</td>
<td>'||i.SBA_EXP_QTY||'</td>
<td>'||i.SBA_BAT_STAT||'</td>
<td>'||i.RECEIPT_NUM||'</td>
<td>'||i.SIT_MKT_VAL||'</td>
<td>'||NVL(TO_CHAR(i.ISH_NO),'--')||'</td>
<td>'||NVL(TO_CHAR(i.ISH_SNO),'--')||'</td>
<td>'||NVL(i.SRC_P_WH,'--')||'</td>
<td>'||NVL(i.DEST_V_WH,'--')||'</td>
<td>'||NVL(i.MOT_CODE,'--')||'</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;
