Item Outbound Sample Code

Below is the sample code for Item Outbound:

CREATE OR REPLACE PROCEDURE EE_INV_ITEM_PROC(
                                    ERRBUF OUT VARCHAR2,
                                    RETCODE OUT VARCHAR2,
                                    P_LOC IN VARCHAR2,
                                    P_FILE_NAME IN VARCHAR2
                                   ) 
IS
CURSOR CUR_INV IS
                 SELECT OOD.ORGANIZATION_NAME,
                        OOD.ORGANIZATION_ID ORID,
                        MSI.SEGMENT1 ITEM_NAME,
                        MIL.CONCATENATED_SEGMENTS ITM_LOC
                  FROM  ORG_ORGANIZATION_DEFINITIONS OOD,
                        MTL_SYSTEM_ITEMS_B MSI,
                        MTL_ITEM_LOCATIONS_KFV MIL                     
                 WHERE  MSI.ORGANIZATION_ID=OOD.ORGANIZATION_ID
                   AND  MIL.ORGANIZATION_ID=MSI.ORGANIZATION_ID
                   AND  TRUNC(MSI.CREATION_DATE)=TRUNC(SYSDATE);
V_FILE_TYPE UTL_FILE.FILE_TYPE;
BEGIN
V_FILE_TYPE:=UTL_FILE.FOPEN(LOCATION  =>P_LOC,
                            FILENAME  =>P_FILE_NAME,
                            OPEN_MODE => 'W');
FOR V_REC IN CUR_INV
LOOP
UTL_FILE.PUT_LINE(V_FILE_TYPE,V_REC.ORGANIZATION_NAME||'~'||
                              V_REC.ORID||'~'||
                              V_REC.ITEM_NAME||'~'||
                              V_REC.ITM_LOC);
END LOOP;
UTL_FILE.FCLOSE(V_FILE_TYPE);
EXCEPTION
WHEN UTL_FILE.invalid_operation THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'FILE HACVING INVALID OPERATION');
WHEN UTL_FILE.invalid_filename  THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE NAME'||P_FILE_NAME);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
END;                                                                        


Hope this will help.    

Workflow scripts for Different Errors

Below are the helpful queries beginners can use to find out errors in oracle workflows:-

1) SELECT COUNT (*)
,item_type
,activity_name
,MIN (item_begin_date)
,MAX (item_begin_date)
FROM wf_item_activity_statuses_v
WHERE activity_status_code = 'ERROR'
AND item_end_date IS NULL
GROUP BY item_type
,activity_name
ORDER BY 3 DESC, 1 DESC, 2;


2) SELECT item_type
,parent_item_type
,DECODE (end_date, NULL, 'OPEN', 'CLOSED')
error_type_status
,COUNT (*)
FROM wf_items
WHERE parent_item_type is not null
AND item_type in ('CUNNLWF','DOSFLOW','DOSFLOWE',
'ECXERROR','HRSSA','HRSTAND','HXCEMP','IBUHPSUB','OKLAMERR',
'OMERROR','PARMAAP','PARMATRX','POERROR','WFSTD','XDPWFSTD',
'ZPBWFERR', 'WFERROR')
GROUP BY item_type
,parent_item_type
,DECODE (end_date, NULL, 'OPEN', 'CLOSED')
ORDER BY item_type,parent_item_type;

3) SELECT COUNT (*)

,v.text_value

,min(i.begin_date)

,max(i.begin_date)

FROM wf_item_attribute_values v

,wf_items i

WHERE v.item_key=i.item_key

AND v.item_type = i.item_type

AND v.item_type = 'WFERROR'

AND v.NAME = 'EVENT_NAME'

AND v.text_value IS NOT NULL

GROUP BY text_value

ORDER BY text_value;