Tuesday, April 22, 2008

UOM Conversion

CREATE OR REPLACE PACKAGE XX_UOM_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE INSERT_DATA_STG;
PROCEDURE INSERT_COLUMN_STG;
PROCEDURE VALIDATE_UOM_STG;
PROCEDURE XX_UOM_IMPORT;
END XX_UOM_PKG;
/


CREATE OR REPLACE PACKAGE BODY XX_UOM_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
INSERT_DATA_STG;
INSERT_COLUMN_STG;
VALIDATE_UOM_STG;
XX_UOM_IMPORT;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END;
PROCEDURE INSERT_DATA_STG
IS
CURSOR CUR_STG
IS
SELECT UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE='IN';
BEGIN
DELETE XX_UOM_CONV_STG;
COMMIT;
FOR REC_CUR_STG IN CUR_STG
LOOP
INSERT INTO XX_UOM_CONV_STG
(UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_FLAG)
VALUES
(REC_CUR_STG.UNIT_OF_MEASURE,
REC_CUR_STG.UOM_CODE,
REC_CUR_STG.UOM_CLASS,
REC_CUR_STG.BASE_UOM_FLAG,
REC_CUR_STG.UNIT_OF_MEASURE_TL,
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
NULL,
REC_CUR_STG.DESCRIPTION,
REC_CUR_STG.LANGUAGE,
REC_CUR_STG.SOURCE_LANG,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N');
END LOOP;
END INSERT_DATA_STG ;

PROCEDURE INSERT_COLUMN_STG IS
BEGIN
INSERT INTO XX_UOM_CONV_STG
(UNIT_OF_MEASURE,
UOM_CODE,
UOM_CLASS,
BASE_UOM_FLAG,
UNIT_OF_MEASURE_TL,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_LOGIN,
DISABLE_DATE,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS_FLAG)
VALUES
('POISE',
'PO',
'VISCOSITY',
'N',
'POISE',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
NULL,
'POISE',
'US',
'US',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N');
COMMIT;
END INSERT_COLUMN_STG;

PROCEDURE VALIDATE_UOM_STG
IS
L_UNIT_OF_MEASURE MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE%TYPE;
L_UOM_CODE MTL_UNITS_OF_MEASURE.UOM_CODE%TYPE;
L_UOM_CLASS MTL_UOM_CLASSES.UOM_CLASS%TYPE;
L_UNIT_OF_MEASURE_TL MTL_UNITS_OF_MEASURE.UNIT_OF_MEASURE_TL%TYPE;
L_LANGUAGE FND_LANGUAGES.LANGUAGE_CODE%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VAL_UOM
IS
SELECT A.*,ROWID
FROM XX_UOM_CONV_STG A
WHERE STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VAL_UOM IN CUR_VAL_UOM
LOOP
BEGIN
SELECT UNIT_OF_MEASURE
INTO L_UNIT_OF_MEASURE
FROM MTL_UNITS_OF_MEASURE
WHERE UNIT_OF_MEASURE = REC_CUR_VAL_UOM.UNIT_OF_MEASURE;
UPDATE XX_UOM_CONV_STG
SET UNIT_OF_MEASURE=L_UNIT_OF_MEASURE
WHERE ROWID= REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || ' ERROR UNIT OF MEASURE';
END;
BEGIN
SELECT LANGUAGE_CODE
INTO L_LANGUAGE
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE=REC_CUR_VAL_UOM.LANGUAGE;
UPDATE XX_UOM_CONV_STG
SET LANGUAGE=L_LANGUAGE
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'LANGUAGE DOES NOT EXIST';
END;
BEGIN
SELECT UOM_CLASS
INTO L_UOM_CLASS
FROM MTL_UOM_CLASSES
WHERE UOM_CLASS = REC_CUR_VAL_UOM.UOM_CLASS;
UPDATE XX_UOM_CONV_STG
SET UOM_CLASS=L_UOM_CLASS
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'UNIT OF MEASURE CLASS DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_UOM_CONV_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
ELSE
UPDATE XX_UOM_CONV_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VAL_UOM.ROWID;
END IF;
COMMIT;
END LOOP;
UPDATE XX_UOM_CONV_STG SET UNIT_OF_MEASURE='SRI',UOM_CODE='SRI';
COMMIT;
END VALIDATE_UOM_STG;

PROCEDURE XX_UOM_IMPORT
IS
L_ROW_ID VARCHAR2(200):= NULL;
L_DISABLE_DATE DATE:=NULL;
L_ATTRIBUTE_CATEGORY VARCHAR2(200):= NULL;
L_ATTRIBUTE1 VARCHAR2(200):= NULL;
L_ATTRIBUTE2 VARCHAR2(200):= NULL;
L_ATTRIBUTE3 VARCHAR2(200):= NULL;
L_ATTRIBUTE4 VARCHAR2(200):= NULL;
L_ATTRIBUTE5 VARCHAR2(200):= NULL;
L_ATTRIBUTE6 VARCHAR2(200):= NULL;
L_ATTRIBUTE7 VARCHAR2(200):= NULL;
L_ATTRIBUTE8 VARCHAR2(200):= NULL;
L_ATTRIBUTE9 VARCHAR2(200):= NULL;
L_ATTRIBUTE10 VARCHAR2(200):= NULL;
L_ATTRIBUTE11 VARCHAR2(200):= NULL;
L_ATTRIBUTE12 VARCHAR2(200):= NULL;
L_ATTRIBUTE13 VARCHAR2(200):= NULL;
L_ATTRIBUTE14 VARCHAR2(200):= NULL;
L_ATTRIBUTE15 VARCHAR2(200):= NULL;
L_REQUEST_ID NUMBER:= 1;
L_PROGRAM_ID NUMBER:= 1;
L_PROGRAM_APPLICATION_ID NUMBER:= 1;
L_PROGRAM_UPDATE_DATE DATE:= SYSDATE;
L_LAST_UPDATE_LOGIN NUMBER:=1;
L_COUNT1 NUMBER;
L_COUNT2 NUMBER;
L_COUNT3 NUMBER;
CURSOR CUR_IMP_UOM IS SELECT * FROM XX_UOM_CONV_STG;
BEGIN
FOR REC_CUR_IMP_UOM IN CUR_IMP_UOM LOOP
SELECT COUNT(*) INTO L_COUNT1 FROM MTL_UOM_CLASSES_TL WHERE UOM_CLASS=REC_CUR_IMP_UOM.UOM_CLASS ;
SELECT COUNT(*) INTO L_COUNT2 FROM MTL_UNITS_OF_MEASURE_TL WHERE UNIT_OF_MEASURE=REC_CUR_IMP_UOM.UNIT_OF_MEASURE;
SELECT COUNT(*) INTO L_COUNT3 FROM MTL_UNITS_OF_MEASURE_TL WHERE UOM_CLASS=REC_CUR_IMP_UOM.UOM_CLASS AND BASE_UOM_FLAG='Y';
IF L_COUNT3 =0 OR (L_COUNT3 IS NOT NULL AND REC_CUR_IMP_UOM.BASE_UOM_FLAG!='Y') THEN
IF L_COUNT1 IS NOT NULL AND L_COUNT2 = 0 THEN
MTL_UNITS_OF_MEASURE_TL_PKG.INSERT_ROW (L_ROW_ID,
REC_CUR_IMP_UOM.UNIT_OF_MEASURE,
REC_CUR_IMP_UOM.UNIT_OF_MEASURE_TL,
L_ATTRIBUTE_CATEGORY,
L_ATTRIBUTE1,
L_ATTRIBUTE2,
L_ATTRIBUTE3,
L_ATTRIBUTE4,
L_ATTRIBUTE5,
L_ATTRIBUTE6,
L_ATTRIBUTE7,
L_ATTRIBUTE8,
L_ATTRIBUTE9,
L_ATTRIBUTE10,
L_ATTRIBUTE11,
L_ATTRIBUTE12,
L_ATTRIBUTE13,
L_ATTRIBUTE14,
L_ATTRIBUTE15,
L_REQUEST_ID,
L_DISABLE_DATE,
REC_CUR_IMP_UOM.BASE_UOM_FLAG,
REC_CUR_IMP_UOM.UOM_CODE,
REC_CUR_IMP_UOM.UOM_CLASS,
REC_CUR_IMP_UOM.DESCRIPTION,
SYSDATE,
'1318',
SYSDATE,
'1318',
'-1',
L_PROGRAM_APPLICATION_ID,
L_PROGRAM_ID,
L_PROGRAM_UPDATE_DATE,
REC_CUR_IMP_UOM.LANGUAGE );
ELSIF L_COUNT1 =0 AND L_COUNT2 IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,REC_CUR_IMP_UOM.UOM_CLASS||' UOM CLASS DOES NOT EXIST
AND ' || REC_CUR_IMP_UOM.UNIT_OF_MEASURE||' UNIT OF MEASURE ALREADY EXISTS');
ELSIF L_COUNT1 =0 AND L_COUNT2 =0 THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'UOM CLASS DOES NOT EXIST');
ELSIF L_COUNT1 IS NOT NULL AND L_COUNT2 IS NOT NULL THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'UNIT OF MEASURE ALREADY EXISTS');
END IF;
ELSE
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'BASE UNIT OF MEASURE ALREADY EXISTS');
END IF;
END LOOP;
COMMIT;
END XX_UOM_IMPORT;
END XX_UOM_PKG;
/

=====================================================
CONCURRENT PROGRAM FOR UOM CONVERSION:
=============================================


MAIN PRG OF CONVERSION(UOM)

==========================================================

To Capture the Information in DFF in purchasing

TO POPULATE THE VALUES IN DESCRIPTIVE FLEX FIELDS:
=====================================================

1)COPY THE CUSTOM.PLL AND KEEP IT IN THE LOCAL DIRECTORY
2)OPEN THE FORM BUILDER
3)OPEN THE APPLICATIONS AND CREATE THE DFF THROUGH APPLICATION DEVELOPER RESPONSIBILITY
4)NOTE THE FORM NAME AND BLOCK NAME
5)WRITE THE FOLLOWING CODE BY GIVING APPROPRIATE FORM NAME AND BLOCK NAME.
6)WRITE A CUSTOM PACKAGE AND WRITE A PROCEDURE INTO IT.
7)RETRIVE THE VALUES FROM THE TABLES THROUGH A SELECT STATEMENT AND STORE IT IN VARIABLES
8)USE "COPY" FUNCTION AND THEN POPULATE INTO THE REQUIRED FIELDS.


CODE WHICH IS TO BE WRITTEN IN CUSTOM.PLL:
============================================

form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
param_to_pass1 varchar2(255);
-- param_to_pass2 varchar2(255);
begin
-- Zoom event opens a new session of a form and
-- passes parameter values to the new session. The parameters
-- already exist in the form being opened.

if (form_name = 'RCVRCERC' and block_name = 'RCV_TRANSACTION') then
if (event_name = 'WHEN-NEW-BLOCK-INSTANCE') then
param_to_pass1 := name_in('HEADER.RECEIVER');
PO_FLEX.P1(param_to_pass1);
END IF;
END IF;
end event;


CODE WHICH IS TO BE WRITTEN IN CUSTOM PACKAGE:
==============================================

PACKAGE BODY PO_FLEX IS
PROCEDURE P1 (param_to_pass1 IN VARCHAR2) IS
B NUMBER(10);
C varchar2(30) ;
D NUMBER(10);
E VARCHAR2(30);
BEGIN
C := name_in('HEADER.RECEIVER');
MESSAGE('ABC');
MESSAGE(' ');
SELECT PAPF.PERSON_ID,PAPF.EMPLOYEE_NUMBER,PAPF.FULL_NAME INTO B,D,E
FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.FULL_NAME = C;
COPY(B,'RCV_TRANSACTION.ATTRIBUTE4');
COPY(D,'RCV_TRANSACTION.ATTRIBUTE4');
COPY(E,'RCV_TRANSACTION.ATTRIBUTE4');
END P1;
END PO_FLEX;

RTV_By Org,by Buyer(xml)

Report Query
select a.quantity,a.po_line_id,a.employee_id,b.full_name,a.organization_id, d.organization_code, d.organization_name,c.item_description,a.vendor_id, a.vendor_site_id,e.vendor_name from rcv_transactions a, po_buyers_all_v b ,po_lines_all c, org_organization_definitions d, ap_vendors_v e where a.po_line_id=c.po_line_id and a.employee_id=b.employee_id and a.vendor_id = e.vendor_id and a.organization_id=d.organization_id and a.transaction_type='RETURN TO VENDOR' &p_where_clause

Report Triggers
1) After Parameter Form Trigger :
function AfterPForm return boolean is
begin
if :P_Organization is null and :P_BuyerName is null then
:p_where_clause:='';
elsif :P_BuyerName is null and :P_Organization is not null then
:p_where_clause:= 'and d.organization_id = :P_Organization';
elsif :P_BuyerName is not null and :P_Organization is null then
:p_where_clause:= 'and b.full_name=:P_BuyerName';
else
:p_where_clause:= 'and d.organization_id = :P_Organization and b.full_name=:P_BuyerName';

end If;
return (TRUE);
end;

Report on order details shipped (X) days

select a.ORDER_NUMBER,a.ORDERED_DATE,a.ORDER_CATEGORY_CODE,a.ORDER_TYPE_ID,a.TRANSACTIONAL_CURR_CODE,
b.LINE_NUMBER,b.ORDERED_ITEM,b.PRICING_QUANTITY_UOM,b.SHIPPED_QUANTITY,b.SHIPMENT_NUMBER,b.REQUEST_DATE,
b.ACTUAL_SHIPMENT_DATE,c.ORGANIZATION_NAME SHIP_FROM_ORG,round(b.actual_shipment_date-b.request_date) days
from
oe_order_headers_all a,
oe_order_lines_all b,
org_organization_definitions c
where a.header_id=b.header_id and
b.SHIP_FROM_ORG_ID=c.ORGANIZATION_ID and
round(b.actual_shipment_date-b.request_date)>:ENTER_THE_DAYS

Report on item categories

SELECT PV.VENDOR_NAME,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1 ITEM,
MSI.DESCRIPTION,
(MB.SEGMENT1||'-'||MB.SEGMENT2||'-'||MB.SEGMENT3) CATEGORY,
MB.CATEGORY_ID,
MT.DESCRIPTION CATEGORYNAME,
MSI.ORGANIZATION_ID,
MCSB.CATEGORY_SET_ID,
MCST.CATEGORY_SET_NAME,
MCSB.STRUCTURE_ID,
PASL.VENDOR_BUSINESS_TYPE
FROM PO_VENDORS PV,
MTL_SYSTEM_ITEMS_B MSI,
PO_APPROVED_SUPPLIER_LIST PASL,
MTL_CATEGORY_SET_VALID_CATS MCSV,
MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B MB,
MTL_CATEGORIES_TL MT,
MTL_CATEGORY_SETS_B MCSB,
MTL_CATEGORY_SETS_TL MCST
WHERE PV.VENDOR_ID=PASL.VENDOR_ID
AND PASL.ITEM_ID=MSI.INVENTORY_ITEM_ID
AND PASL.OWNING_ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MIC.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MB.CATEGORY_ID=MIC.CATEGORY_ID
AND MT.CATEGORY_ID=MIC.CATEGORY_ID
AND MCSV.CATEGORY_ID=MIC.CATEGORY_ID
AND MCSV.CATEGORY_SET_ID=MIC.CATEGORY_SET_ID
AND MCSV.CATEGORY_SET_ID=MCSB.CATEGORY_SET_ID
AND MCSV.CATEGORY_SET_ID=MCST.CATEGORY_SET_ID
AND VENDOR_NAME=:VENDORNAME

Report On Inv Balances(xml)

select msi.segment1,mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',-primary_quantity,null)),0) issue,
nvl(sum(decode(substr(mmt.primary_quantity,1,1),'-',null,primary_quantity)),0) receipt
from mtl_material_transactions mmt, mtl_system_items msi, org_organization_definitions ood
where
mmt.organization_id=msi.organization_id and
mmt.inventory_item_id=msi.inventory_item_id and
mmt.organization_id=ood.organization_id and
mmt.organization_id=:org and
mmt.transaction_date between :d1 and :d2
group by msi.segment1, mmt.inventory_item_id,mmt.subinventory_code,ood.organization_name,mmt.organization_id
order by msi.segment1

PO Outstand Balance

query1:
=======
SELECT PHA.PO_HEADER_ID,
PHA.SEGMENT1 "PO Number",
SUM(PLA.UNIT_PRICE*PLA.QUANTITY) "PO Total"
FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA
WHERE
PHA.PO_HEADER_ID IN( PLA.PO_HEADER_ID)
AND PHA.TYPE_LOOKUP_CODE ='STANDARD'
GROUP BY
PHA.PO_HEADER_ID,PHA.SEGMENT1


query2:
=======
SELECT
DISTINCT(AIA.INVOICE_NUM) "Invoice Number",
AIA.INVOICE_DATE "Invoice Date",
AIA.INVOICE_AMOUNT "Invoice Amount",
AIA.AMOUNT_PAID "Amount Paid",
PDA.PO_HEADER_ID
FROM
AP_INVOICES_ALL AIA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_DISTRIBUTIONS_ALL PDA
WHERE
AIA.INVOICE_ID IN (AIDA.INVOICE_ID)
AND
AIDA.PO_DISTRIBUTION_ID IN(PDA.PO_DISTRIBUTION_ID);

PO Conversion

-----------------------------------------------------------------------------
STAGING TABLES
-----------------------------------------------------------------------------
**********************************************************
create table xx_po_headers(
INTERFACE_HEADER_ID number(10)
,BATCH_ID varchar2(25)
,ACTION varchar2(25)
,ORG_CODE VARCHAR2(25)
,DOCUMENT_TYPE_CODE VARCHAR2(50)
,CURRENCY_CODE VARCHAR2(25)
,AGENT_NAME VARCHAR2(25)
,VENDOR_NAME VARCHAR2(50)
,VENDOR_SITE_CODE VARCHAR2(50)
,SHIP_TO_LOCATION VARCHAR2(50)
,BILL_TO_LOCATION VARCHAR2(50)
,APPROVAL_STATUS VARCHAR2(50)
,FREIGHT_CARRIER VARCHAR2(50)
,FOB VARCHAR2(50)
,FREIGHT_TERMS VARCHAR2(50)
,STATUS_FLAG VARCHAR2(3)
,ERR_MSG VARCHAR2(1000));
***********************************************************

CREATE TABLE XX_PO_LINES(
interface_header_id NUMBER(10)
,interface_line_id NUMBER(10)
,LINE_NUM NUMBER(10)
,ACTION varchar2(25)
,SHIPMENT_NUM NUMBER(10)
,LINE_TYPE VARCHAR2(50)
,ITEM VARCHAR2(50)
,ITEM_DESCRIPTION VARCHAR2(100)
,item_id NUMBER(10)
,UOM_CODE VARCHAR2(50)
,QUANTITY NUMBER(10)
,UNIT_PRICE NUMBER(10)
,SHIP_TO_ORGANIZATION_CODE VARCHAR2(50)
,SHIP_TO_LOCATION VARCHAR2(50)
,list_price_per_unit NUMBER(10),
NEED_BY_DATE DATE,
PROMISED_DATE DATE,
CATEGORY_ID VARCHAR2(50),
STATUS_FLAG VARCHAR2(3),
ERR_MSG VARCHAR2(1000));
************************************************************

create table xx_po_distributions(
interface_header_id NUMBER(10)
,interface_line_id NUMBER(10),
interface_distribution_id number(10),
DIS_NUM number(10),
QUANTITY number(10),
DES_ORG varchar2(30),
DES_TYPE varchar2(50),
SUBINV VARCHAR2(50),
DELEIVER_TO VARCHAR2(50),
REQUESTOR VARCHAR2(100),
STATUS_FLAG VARCHAR2(3),
ERR_MSG VARCHAR2(1000));

***************************************************************
CREATE OR REPLACE PROCEDURE ABC (Errbuf OUT
VARCHAR2,
Retcode OUT VARCHAR2)
AS
i number := 1;
j number :=1;
K number :=1;

CURSOR C1 IS select mp.organization_code
ORG_CODE,pha.TYPE_LOOKUP_CODE
DOCUMENT_TYPE_CODE ,pha.CURRENCY_CODE
CURRENCY_CODE,ppf.FULL_NAME AGENT_NAME
,pv.VENDOR_name
VENDOR_NAME,pvs.VENDOR_SITE_CODE
VENDOR_SITE_CODE ,
hr1.LOCATION_CODE ship_to,hr2.LOCATION_CODE
bill_to,pha.SHIP_VIA_LOOKUP_CODE
FREIGHT_CARRIER,PHA.PO_HEADER_ID
PO_HEADER_ID1,
pha.FOB_LOOKUP_CODE FOB
,pha.FREIGHT_TERMS_LOOKUP_CODE
FREIGHT_TERMS
from po_headers_all pha ,per_all_people_f
ppf,po_vendors pv,po_vendor_sites_all pvs
,hr_locations_all hr1,
hr_locations_all hr2,mtl_parameters mp
where pha.segment1 IN (4491,4475)
and pha.AGENT_ID=ppf.PERSON_ID
and pha.VENDOR_ID=pv.VENDOR_ID
and pha.VENDOR_SITE_ID=pvs.VENDOR_SITE_ID
and pha.SHIP_TO_LOCATION_ID=hr1.LOCATION_ID
and pha.BILL_TO_LOCATION_ID=hr2.LOCATION_ID
and pha.ORG_ID=mp.ORGANIZATION_ID;


CURSOR C2(HEADER_ID VARCHAR2) IS select
PLL.PO_LINE_ID
PO_LINE_ID1,PLA.LINE_LOCATION_ID
LINE_LOCATION_ID1,pll.LINE_NUM
line_num,pla.SHIPMENT_NUM
SHIP_NUM,plt.PURCHASE_BASIS LINE_TYPE
,msi.SEGMENT1
ITEM,pll.ITEM_DESCRIPTION DESP,
MSI.PRIMARY_UOM_CODE
UOM_CODE,plA.QUANTITY
QUANTITY,pll.LIST_PRICE_PER_UNIT
LIST_PRICE,pll.UNIT_PRICE UNIT_PRICE,
mp.ORGANIZATION_CODE
SHIP_TO_ORGANIZATION_CODE
,hr.LOCATION_CODE SHIP_TO_LOCATION,
PLL.CATEGORY_ID CATEGORY_ID
from po_lines_all pll,PO_LINE_TYPES_B
plt,mtl_system_items_b msi,po_line_locations_all
pla,mtl_parameters mp,
hr_locations hr
where PLA.PO_HEADER_ID=HEADER_ID
AND Pll.LINE_TYPE_ID=plt.LINE_TYPE_ID
and pll.ITEM_ID=msi.INVENTORY_ITEM_ID
and pll.ORG_ID=msi.ORGANIZATION_ID
and pll.PO_LINE_ID=pla.PO_LINE_ID
and pll.PO_HEADER_ID=PLA.PO_HEADER_ID
and
pla.SHIP_TO_ORGANIZATION_ID=mp.ORGANIZATION_ID
and pla.SHIP_TO_LOCATION_ID=hr.LOCATION_ID;


cursor c3 (header_id varchar2,line_id
varchar2,L_LOCATION_ID VARCHAR2) is select
pda.DISTRIBUTION_NUM
dis_num,pda.QUANTITY_ORDERED
qUANTITY,mp.ORGANIZATION_CODE
des_org,pda.DESTINATION_TYPE_CODE
des_type,pda.DESTINATION_SUBINVENTORY subinv,
hr.LOCATION_CODE deliver_to,ppf.FULL_NAME
requestor
from po_distributions_all pda,hr_locations_all hr
,per_all_people_f ppf,mtl_parameters mp
where pda.po_header_id =header_id
and pda.po_line_id=line_id
AND PDA.LINE_LOCATION_ID=L_LOCATION_ID
and
pda.DELIVER_TO_LOCATION_ID=hr.LOCATION_ID(
+)
and
pda.DELIVER_TO_PERSON_ID=ppf.PERSON_ID(+)
and pda.DESTINATION_ORGANIZATION_ID
=mp.ORGANIZATION_ID;

BEGIN

DELETE FROM xx_po_headers;
DELETE FROM XX_PO_LINES;
DELETE FROM xx_po_distributions;

COMMIT;

FOR REC_C1 IN C1 LOOP

INSERT INTO
xx_po_headers(INTERFACE_HEADER_ID,BATCH_ID
,ACTION,ORG_CODE,DOCUMENT_TYPE_CODE,CURRENCY_CODE,AGENT_NAME,VENDOR_NAME,
VENDOR_SITE_CODE
,SHIP_TO_LOCATION,BILL_TO_LOCATION,APPROVAL_STATUS
,FREIGHT_CARRIER ,FOB
,FREIGHT_TERMS,STATUS_FLAG ) VALUES
(i,100,'ORIGINAL',REC_C1.ORG_CODE,REC_C1.DOCUMENT_TYPE_CODE ,
REC_C1.CURRENCY_CODE,REC_C1.AGENT_NAME
,REC_C1.VENDOR_NAME,REC_C1.VENDOR_SITE_CODE
,REC_C1.ship_to,REC_C1.bill_to,'APPROVED',REC_C1.FREIGHT_CARRIER,REC_C1.FOB
,REC_C1.FREIGHT_TERMS,'N');

FOR REC_C2 IN C2(REC_C1.PO_HEADER_ID1) LOOP

INSERT INTO XX_PO_LINES(interface_header_id
,interface_line_id,LINE_NUM,ACTION,SHIPMENT_NUM,LINE_TYPE ,ITEM,ITEM_DESCRIPTION ,UOM_CODE,QUANTITY,UNIT_PRICE,SHIP_TO_ORGANIZATION_CODE,SHIP_TO_LOCATION,list_price_per_unit,NEED_BY_DATE
,PROMISED_DATE,STATUS_FLAG,CATEGORY_ID )
VALUES (i,J,REC_C2.line_num,'ORIGINAL',
REC_C2.SHIP_NUM,REC_C2.LINE_TYPE,REC_C2.ITEM
,REC_C2.DESP,REC_C2.UOM_CODE
,REC_C2.QUANTITY,
REC_C2.UNIT_PRICE,REC_C2.SHIP_TO_ORGANIZATION_CODE
,REC_C2.SHIP_TO_LOCATION,REC_C2.LIST_PRICE,SYSDATE+10,SYSDATE+10,'N',REC_C2.CATEGORY_ID
);


FOR REC_C3 IN
C3(REC_C1.PO_HEADER_ID1,REC_C2.PO_LINE_ID1,
REC_C2.LINE_LOCATION_ID1) LOOP

INSERT INTO
xx_po_distributions(interface_header_id,interface_line_id ,interface_distribution_id,DIS_NUM,
QUANTITY, DES_ORG,
DES_TYPE,SUBINV,DELEIVER_TO,REQUESTOR,STATUS_FLAG)
values
(i,j,k,rec_c3.dis_num,rec_c3.qUANTITY,rec_c3.des_org,rec_c3.des_type,rec_c3.subinv,
rec_c3.deliver_to,rec_c3.requestor,'N');
k:=k+1;
end loop;
j:=j+1;
END LOOP;

commit;
i:=i+1;
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
RETCODE:=1;
END;


*************************************
VALIDATIN NOT DONE FOR SHIP_TO_LOCATION,CATEGORY_ID IN LINES AND DES_TYPE,SUBINV
******************************************

********************************************************************************
CREATE OR REPLACE procedure abc_validate1(Errbuf OUT
VARCHAR2,
Retcode OUT VARCHAR2)
AS
cursor c1 is select A.*,ROWID from xx_po_headers A WHERE A.STATUS_FLAG='N';

CURSOR C2(I_HEADER_ID NUMBER) IS select B.*,ROWID from XX_PO_LINES B
WHERE B.INTERFACE_HEADER_ID=I_HEADER_ID AND B.STATUS_FLAG='N';

cursor c3(I_HEADER_ID NUMBER,I_LINE_ID NUMBER) is select c.*,ROWID FROM
xx_po_distributions C WHERE C.INTERFACE_HEADER_ID=I_HEADER_ID AND C.INTERFACE_LINE_ID=I_LINE_ID
AND C.STATUS_FLAG='N';

CURSOR V_c1 is select A.*,ROWID from xx_po_headers A WHERE A.STATUS_FLAG='P';

CURSOR V_C2(I_HEADER_ID NUMBER) IS select B.*,ROWID from XX_PO_LINES B
WHERE B.INTERFACE_HEADER_ID=I_HEADER_ID ;

cursor V_c3(I_HEADER_ID NUMBER,I_LINE_ID NUMBER) is select c.*,ROWID FROM
xx_po_distributions C WHERE C.INTERFACE_HEADER_ID=I_HEADER_ID AND C.INTERFACE_LINE_ID=I_LINE_ID;

l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) := NULL;
l_msg varchar2(1000):= NULL;
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);
l_USER_ID NUMBER(20);
s_loc_id number(20);
B_loc_id number(20);
I NUMBER:=1;
L_TERM VARCHAR2(40);
L_CARRIER VARCHAR2(40);
L_FOB VARCHAR2(20);
L_LINE_TYPE VARCHAR2(20);
L_UOM_CODE VARCHAR2(20);
L_SHIP_TO_ORGANIZATION_CODE VARCHAR2(10);
L_DELEIVER_TO VARCHAR2(40);
R_USER_ID VARCHAR2(20);
L_DOCUMENT_SUBTYPE VARCHAR2(30);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE;
COMMIT;
for rec_c1 in c1 loop
l_flag := null;
l_msg := null;
-- VALIDATION FOR ORGANIZATION
Begin
select organization_id
into l_org_id
from mtl_parameters
where ORGANIZATION_CODE = rec_c1.org_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR CURRENCY
begin
select currency_code
into l_curr_code
from fnd_currencies
where currency_code = rec_c1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Currency Code is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--VALIDATION FOR DOCUMENT TYPE
BEGIN
SELECT DOCUMENT_SUBTYPE INTO L_DOCUMENT_SUBTYPE FROM PO_DOCUMENT_TYPES_ALL_B
WHERE DOCUMENT_TYPE_CODE='PO' AND ORG_ID =FND_PROFILE.VALUE('ORG_ID')
AND DOCUMENT_SUBTYPE=REC_C1.DOCUMENT_TYPE_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'DOCUMENT TYPE is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR AGENT
BEGIN
SELECT PERSON_ID INTO l_USER_ID FROM per_all_people_f where full_name=REC_C1.AGENT_NAME;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'BUYER DOESNOT EXISTS ';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR VENDOR
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = rec_c1.VENDOR_NAME;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Vendor is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR VENDOR SITE
begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code =
rec_c1.VENDOR_SITE_CODE
and VENDOR_ID=l_vendor_id
and ORG_ID=l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Vendor Site Code is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR SHIP TO LOCATION
BEGIN
select LOCATION_ID INTO s_loc_id
from hr_locations_all where location_code=REC_C1.SHIP_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'SHIP TO LOCATION is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR BILL TO LOCATION
BEGIN
select LOCATION_ID INTO B_loc_id
from hr_locations_all where location_code=REC_C1.BILL_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'BILL TO LOCATION is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--VALIDATIION FOR FRIEGHT TERMS
BEGIN
SELECT MEANING INTO L_TERM FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='FREIGHT TERMS' AND MEANING=REC_C1.FREIGHT_TERMS;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'FREIGHT TERMS is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--VALIDATION FOR FREIGHT_CARRIER
BEGIN
SELECT MEANING INTO L_CARRIER FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='FREIGHT_CARRIER' AND MEANING=REC_C1.FREIGHT_CARRIER;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'FREIGHT CARRIER is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--VALIDATION FOR FOB
BEGIN
SELECT MEANING INTO L_FOB FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='FOB' AND MEANING=REC_C1.FOB AND TAG='Y';
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'FOB is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
IF l_flag is Null THEN
UPDATE XX_PO_HEADERS SET STATUS_FLAG='P' WHERE ROWID=REC_C1.ROWID;
ELSE
UPDATE XX_PO_HEADERS SET STATUS_FLAG='E',
ERR_MSG=l_msg WHERE ROWID=REC_C1.ROWID;
UPDATE XX_PO_HEADERS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN HEADER NUMBER'||REC_C1.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C1.INTERFACE_HEADER_ID;
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=ERR_MSG||'ERROR IN HEADER NUMBER'||REC_C1.INTERFACE_HEADER_ID WHERE INTERFACE_HEADER_ID=REC_C1.INTERFACE_HEADER_ID;
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN HEADER NUMBER'||REC_C1.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C1.INTERFACE_HEADER_ID;
RETCODE :=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR IN HEADER NUMBER'||' : '||REC_C1.INTERFACE_HEADER_ID);
END IF;
COMMIT;
l_flag := null;
l_msg := null;
FOR REC_C2 IN C2(REC_C1.INTERFACE_HEADER_ID) LOOP
l_flag := null;
l_msg := null;
--Item,ITEM DISCRITION Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = REC_C2.item
AND ORGANIZATION_ID
=FND_PROFILE.VALUE('ORG_ID')
AND DESCRIPTION=REC_C2.ITEM_DESCRIPTION;
exception
when others then
l_flag := 'E';
l_msg := l_MSG||'Item is not valid Item'||sqlerrm;
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;

-- VALIDATION FOR UOM
BEGIN
SELECT UOM_CODE INTO L_UOM_CODE from MTL_UNITS_OF_MEASURE_TL WHERE UOM_CODE=REC_C2.UOM_CODE;
exception
when others then
l_flag := 'E';
l_msg := l_MSG||'UOM IS NOT DEFINED'||sqlerrm;
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--VALIDATION FOR SHIP_TO_ORGANIZATION_CODE
BEGIN
SELECT ORGANIZATION_CODE INTO L_SHIP_TO_ORGANIZATION_CODE FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE=REC_C2.SHIP_TO_ORGANIZATION_CODE;
exception
when others then
l_flag := 'E';
l_msg := l_MSG||'SHIP TO ORGANIZATION CODE IS NOT DEFINED'||sqlerrm;
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;

IF l_flag is Null THEN
UPDATE XX_PO_LINES SET STATUS_FLAG='P' WHERE ROWID=REC_C2.ROWID;
ELSE
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=l_msg WHERE ROWID=REC_C2.ROWID;
UPDATE XX_PO_HEADERS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN LINE NUMBER'||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'|| REC_C2.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C2.INTERFACE_HEADER_ID;
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=ERR_MSG||'ERROR IN LINE NUMBER'||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'|| REC_C2.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C2.INTERFACE_HEADER_ID and ROWID <> REC_C2.ROWID;
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN LINE NUMBER'||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'|| REC_C2.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C2.INTERFACE_HEADER_ID;
RETCODE := 1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR IN LINE NUMBER'||' : '||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||' : '|| REC_C2.INTERFACE_HEADER_ID);
END IF;
COMMIT;
l_flag := null;
l_msg := null;
FOR REC_C3 IN C3(REC_C2.INTERFACE_HEADER_ID,REC_C2.INTERFACE_LINE_ID) LOOP
l_flag := null;
l_msg := null;
--VALIDATION FOR DES_ORG
BEGIN
select organization_id
into l_org_id
from mtl_parameters
where ORGANIZATION_CODE = rec_c3.DES_ORG;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--VALIDATION FOR DELIVER TO LOCATION

IF REC_C3.DELEIVER_TO IS NOT NULL THEN
BEGIN
SELECT LOCATION_CODE INTO L_DELEIVER_TO FROM HR_LOCATIONS WHERE LOCATION_CODE=REC_C3.DELEIVER_TO;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Invalid DELIVER TO LOCATION';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
END IF;
-- VALIDATION FOR REQUESTOR
IF REC_C3.REQUESTOR IS NOT NULL THEN
BEGIN
SELECT PERSON_ID INTO R_USER_ID FROM per_all_people_f where full_name=REC_C3.REQUESTOR;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'REQUESTOR DOESNOT EXISTS ';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
END IF;
IF l_flag is Null THEN
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG='P' WHERE ROWID=REC_C3.ROWID;
ELSE
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG='E',
ERR_MSG=l_msg WHERE ROWID=REC_C3.ROWID;
UPDATE XX_PO_HEADERS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN DISTRIBUTIONS NUMBER'||REC_C3.INTERFACE_DISTRIBUTION_ID ||'IN LINE NUMBER'||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||REC_C3.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C3.INTERFACE_HEADER_ID;
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=ERR_MSG||'ERROR IN DISTRIBUTIONS NUMBER'||REC_C3.INTERFACE_DISTRIBUTION_ID ||'IN LINE NUMBER'||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||REC_C3.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C3.INTERFACE_HEADER_ID;
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN DISTRIBUTIONS NUMBER'||REC_C3.INTERFACE_DISTRIBUTION_ID||'IN LINE NUMBER'||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||REC_C3.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C3.INTERFACE_HEADER_ID and ROWID <> REC_C3.ROWID;
RETCODE :=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR IN DISTRIBUTIONS NUMBER'||' : '||REC_C3.INTERFACE_DISTRIBUTION_ID||'IN LINE NUMBER'||' : '||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||' : '|| REC_C3.INTERFACE_HEADER_ID);
END IF;
COMMIT;
END LOOP;
l_flag := null;
l_msg := null;
END LOOP;
l_flag := null;
l_msg := null;
END LOOP;
For V_rec_c1 in V_c1 loop
select organization_id
into l_org_id
from mtl_parameters
where ORGANIZATION_CODE = V_rec_c1.org_code;
INSERT INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
,FREIGHT_CARRIER
,FOB
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
V_rec_c1.INTERFACE_HEADER_ID
,V_rec_c1.BATCH_ID
,V_rec_c1.ACTION
,l_org_id
,V_rec_c1.DOCUMENT_TYPE_CODE
,V_rec_c1.CURRENCY_CODE
,V_rec_c1.AGENT_NAME
,V_rec_c1.VENDOR_NAME
,V_rec_c1.VENDOR_SITE_CODE
,'5_VIKAS_INV_LOCATION'
,V_rec_c1.BILL_TO_LOCATION
,SYSDATE
,V_rec_c1.APPROVAL_STATUS
,SYSDATE
,V_rec_c1.FREIGHT_TERMS
,V_rec_c1.FREIGHT_CARRIER,
V_REC_C1.FOB,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
-1
);
FOR V_REC_C2 IN V_C2(V_REC_C1.INTERFACE_HEADER_ID) LOOP
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,ACTION
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,creation_date
,CATEGORY_ID
)
VALUES
(
V_REC_C2.INTERFACE_LINE_ID
,V_rec_c2.ACTION
,V_REC_C2.INTERFACE_HEADER_ID
,V_REC_C2.LINE_NUM
,V_REC_C2.SHIPMENT_NUM
,INITCAP(V_REC_C2.LINE_TYPE)
,V_REC_C2.ITEM
,V_REC_C2.ITEM_DESCRIPTION
,V_REC_C2.UOM_CODE
,V_REC_C2.QUANTITY,
V_REC_C2.UNIT_PRICE
,V_REC_C2.SHIP_TO_ORGANIZATION_CODE
,V_REC_C2.NEED_BY_DATE
,V_REC_C2.PROMISED_DATE
,V_REC_C2.LIST_PRICE_PER_UNIT
,FND_PROFILE.VALUE('USER_ID')
,SYSDATE
,FND_PROFILE.VALUE('USER_ID')
,-1
,SYSDATE
,V_REC_C2.CATEGORY_ID);
FOR V_REC_C3 IN V_C3(V_REC_C2.INTERFACE_HEADER_ID,V_REC_C2.INTERFACE_LINE_ID) LOOP
INSERT INTO PO_distributions_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
ORG_ID,
QUANTITY_ORDERED,
DESTINATION_ORGANIZATION,
DESTINATION_TYPE_CODE,
DELIVER_TO_LOCATION,
DELIVER_TO_PERSON_FULL_NAME,
DESTINATION_SUBINVENTORY)
values
(V_REC_C3.INTERFACE_HEADER_ID,
V_REC_C3.INTERFACE_LINE_ID,
V_REC_C3.INTERFACE_DISTRIBUTION_ID,
V_REC_C3.DIS_NUM,
FND_PROFILE.VALUE('ORG_ID'),
V_REC_C3.QUANTITY,
V_REC_C3.DES_ORG,
V_REC_C3.DES_TYPE,
V_REC_C3.DELEIVER_TO,
V_REC_C3.REQUESTOR,
V_REC_C3.SUBINV);
END LOOP;
END LOOP;
COMMIT;
END LOOP;
END;

Outbound Interface of Reversed Journals

/*XX_REV_JE_CATEGORY*/




SELECT GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJH.REVERSED_JE_HEADER_ID,
GJH.JE_BATCH_ID,
GJH.SET_OF_BOOKS_ID,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJH.NAME,
GJH.CURRENCY_CODE,
GJH.CREATION_DATE,
GJH.DESCRIPTION,
GJH.POSTED_DATE,
GJH.EARLIEST_POSTABLE_DATE,
GJH.STATUS,
GJL.CODE_COMBINATION_ID,
GJL.INVOICE_AMOUNT,
GJL.JE_LINE_NUM,
GJL.PERIOD_NAME,
GJL.STATUS
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.REVERSED_JE_HEADER_ID IS NOT NULL
AND GJH.JE_CATEGORY=:P_CATEGORY;

CREATE OR REPLACE PROCEDURE XX_REV_JOURNAL
(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
P_CATEGORY IN VARCHAR2
)
AS
CURSOR C1 IS SELECT GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJH.REVERSED_JE_HEADER_ID,
GJH.JE_BATCH_ID,
GJH.SET_OF_BOOKS_ID,
GJH.JE_CATEGORY,
GJH.JE_SOURCE,
GJH.NAME,
GJH.CURRENCY_CODE,
GJH.CREATION_DATE,
GJH.DESCRIPTION,
GJH.POSTED_DATE,
GJH.EARLIEST_POSTABLE_DATE,
GJH.STATUS,
GJL.CODE_COMBINATION_ID,
GJL.INVOICE_AMOUNT,
GJL.PERIOD_NAME,
GJL.STATUS
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL
WHERE GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.REVERSED_JE_HEADER_ID IS NOT NULL
AND GJH.JE_CATEGORY=:P_CATEGORY;
REV_ID UTL_FILE.FILE_TYPE;
--L_ID NUMBER(8);
BEGIN
--L_ID :=FND_PROFILE.VALUE('USER_ID');
--FND_FILE.PUT_LINE(FND_FILE.OUTPUT,L_ID);
--DBMS_OUTPUT.PUT_LINE(L_ID);

/*SELECT SUBSTR(VALUE,1,INSTR(VALUE,',')-1)
FROM V$PARAMETER
WHERE UPPER(NAME) LIKE UPPER('UTL_FILE_DIR') */


REV_ID:=UTL_FILE.FOPEN('C:\temp','REV_JE_CATEGORY.TXT','W');
FOR C2 IN C1 LOOP
UTL_FILE.PUT_LINE(REV_ID, C2.JE_HEADER_ID || ',' ||
C2.JE_LINE_NUM || ',' ||
C2.REVERSED_JE_HEADER_ID || ',' ||
C2.JE_BATCH_ID || ',' ||
C2.SET_OF_BOOKS_ID || ',' ||
C2.JE_CATEGORY || ',' ||
C2.JE_SOURCE || ',' ||
C2.CURRENCY_CODE || ',' ||
C2.CREATION_DATE || ',' ||
C2.DESCRIPTION || ',' ||
C2.POSTED_DATE || ',' ||
C2.EARLIEST_POSTABLE_DATE || ',' ||
C2.STATUS || ',' ||
C2.CODE_COMBINATION_ID || ',' ||
C2.INVOICE_AMOUNT || ',' ||
C2.PERIOD_NAME || ',' ||
C2.STATUS);
END LOOP;
UTL_FILE.FCLOSE(REV_ID);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
END XX_REV_JOURNAL;

Orders Conversion as Booked

CREATE TABLE XX_HEADER_STG
(
HEADER_ID NUMBER(10),
ORDER_NUMBER VARCHAR2(40),
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50),
VERSION_NUMBER NUMBER,
ORG_ID NUMBER,
CUSTOMER_NUMBER VARCHAR2(30),
ORDERED_DATE DATE,
ORDER_TYPE_ID NUMBER,
SALESREP_ID NUMBER(20),
NAME VARCHAR2(240),
PRICE_LIST_ID NUMBER,
CONVERSION_RATE NUMBER,
CONVERSION_TYPE_CODE VARCHAR2(30),
TAX_EXEMPT_REASON_CODE VARCHAR2(30),
BOOKED_FLAG VARCHAR2(1),
BOOKED_DATE DATE,
OPEN_FLAG VARCHAR2(1),
TRANSACTIONAL_CURR_CODE VARCHAR2(15),
INVOICING_RULE_ID NUMBER(15),
PAYMENT_TERM_ID NUMBER(15),
PARTY_NAME VARCHAR2(360),
SHIPMENT_PRIORITY_CODE VARCHAR2(30),
SHIPPING_METHOD_CODE VARCHAR2(30),
FOB_POINT_CODE VARCHAR2(30),
FREIGHT_TERMS_CODE VARCHAR2(30),
DESCRIPTION VARCHAR2(240),
SHIP_TO_ADDRESS VARCHAR2(240),
BILL_TO_ADDRESS VARCHAR2(240),
CITY VARCHAR2(60),
SHIPTO NUMBER(15),
BILLTO NUMBER(15),
SOLD_TO_CONTACT_ID NUMBER,
SALES_CHANNEL_CODE VARCHAR2(30),
ORDER_CATEGORY_CODE VARCHAR2(30),
SOLD_FROM_ORG_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
DELIVER_TO_ORG_ID NUMBER,
REQUEST_ID NUMBER,
STATUS_FLAG VARCHAR2(1),
ERROR_MESSAGE VARCHAR2(1000)
);
========================================================================

CREATE TABLE XX_LINE_STG
(
HEADER_ID NUMBER(10),
LINE_ID NUMBER(10),
LINE_TYPE_ID NUMBER,
LINE_NUMBER NUMBER,
ORG_ID NUMBER,
SEGMENT1 VARCHAR2(40),
PRICING_QUANTITY NUMBER,
PRICING_QUANTITY_UOM VARCHAR2(3),
ORDERED_QUANTITY NUMBER,
ORDER_QUANTITY_UOM VARCHAR2(3),
SHIPPING_QUANTITY NUMBER,
SHIPPING_QUANTITY_UOM VARCHAR2(3),
SHIPMENT_NUMBER NUMBER,
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2 (50),
ORIG_SYS_LINE_REF VARCHAR2 (50),
ORIG_SYS_SHIPMENT_REF VARCHAR2(50),
INVENTORY_ITEM_ID NUMBER,
ORGANIZATION_ID NUMBER,
SALESREP_ID NUMBER(20),
SNAME VARCHAR2(240),
ACCOUNTING_RULE_ID NUMBER,
SOURCE_TYPE_CODE VARCHAR2 (30),
SHIPMENT_PRIORITY_CODE VARCHAR2(30),
FREIGHT_TERMS_CODE VARCHAR2(30),
FOB_POINT_CODE VARCHAR2(30),
UNIT_SELLING_PRICE NUMBER,
UNIT_LIST_PRICE NUMBER,
PAYMENT_TERM_ID NUMBER(15),
NAME VARCHAR2(15),
PRICE_LIST_ID NUMBER,
INVOICING_RULE_ID NUMBER(15),
TAX_VALUE NUMBER,
UNIT_COST NUMBER,
DELIVERY_LEAD_TIME NUMBER,
SHIP_TO_ADDRESS VARCHAR2(240),
BILL_TO_ADDRESS VARCHAR2(240),
CITY VARCHAR2(60),
BILLTO NUMBER(15),
SHIPTO NUMBER(15),
SOLD_FROM_ORG_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
BOOKED_FLAG VARCHAR2(1),
OPEN_FLAG VARCHAR2(1),
REQUEST_ID NUMBER,
REFERENCE_TYPE VARCHAR2(30),
REFERENCE_HEADER_ID NUMBER,
REFERENCE_LINE_ID NUMBER,
DEMAND_CLASS_CODE VARCHAR2(30),
STATUS_FLAG VARCHAR2 (1) ,
ERROR_MESSAGE VARCHAR2(1000)
);

===========================================================================
SELECT OOH.HEADER_ID,
MSO.SEGMENT1,
OOS.ORDER_SOURCE_ID,
OOH.ORIG_SYS_DOCUMENT_REF,
OTT.TRANSACTION_TYPE_ID,
OOH.VERSION_NUMBER,
OOH.ORG_ID,
OOH.ORDERED_DATE,
HRO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
OOH.SOLD_TO_ORG_ID,
OOH.DELIVER_TO_ORG_ID,
OOH.SOLD_TO_CONTACT_ID,
RSA.SALESREP_ID,
RSA.NAME,
OOH.TRANSACTIONAL_CURR_CODE,
HCA.ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.SHIPPING_METHOD_CODE,
OTT.FOB_POINT_CODE,
SPL.PRICE_LIST_ID,
SPL.FREIGHT_TERMS_CODE,
SPL.DESCRIPTION,
OOH.CONVERSION_RATE,
OOH.CONVERSION_TYPE_CODE,
OOH.TAX_EXEMPT_REASON_CODE,
OOH.BOOKED_FLAG,
OOH.BOOKED_DATE,
OOH.OPEN_FLAG,
RAR.RULE_ID,
RTB.TERM_ID,
OOH.SALES_CHANNEL_CODE,
OOH.ORDER_CATEGORY_CODE,
OOH.REQUEST_ID
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_SOURCES OOS,
MTL_SALES_ORDERS MSO,
OE_TRANSACTION_TYPES_ALL OTT,
HR_ALL_ORGANIZATION_UNITS HRO,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1,
SO_PRICE_LISTS_B SPL,
RA_SALESREPS_ALL RSA,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
RA_RULES RAR,
RA_TERMS_B RTB
WHERE OOH.ORDER_NUMBER=MSO.SEGMENT1
AND OOH.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
AND OOH.SOLD_FROM_ORG_ID=HRO.ORGANIZATION_ID
AND OOH.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOH.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND OOH.SALESREP_ID=RSA.SALESREP_ID
AND OOH.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOH.INVOICING_RULE_ID=RAR.RULE_ID
AND OOH.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOH.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND RSA.ORG_ID='204'
AND OOH.ORDER_NUMBER='56701';

================================================================

SELECT OOL.HEADER_ID,
OOL.LINE_ID,
OOL.LINE_NUMBER,
OOL.LINE_TYPE_ID,
OOL.SHIPMENT_NUMBER,
OOL.ORG_ID,
OOS.ORDER_SOURCE_ID,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
OOL.ORIG_SYS_DOCUMENT_REF,
OOL.ORIG_SYS_LINE_REF,
OOL.ORIG_SYS_SHIPMENT_REF,
OOL.PRICING_QUANTITY,
OOL.PRICING_QUANTITY_UOM,
OOL.ORDERED_QUANTITY,
OOL.ORDER_QUANTITY_UOM,
OOL.SHIPPING_QUANTITY,
OOL.SHIPPING_QUANTITY_UOM,
RSA.SALESREP_ID,
RSA.NAME SNAME,
OOL.SOURCE_TYPE_CODE,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.FREIGHT_TERMS_CODE,
OTT.FOB_POINT_CODE,
OOL.UNIT_SELLING_PRICE,
OOL.UNIT_LIST_PRICE,
RTB.TERM_ID,
RTT.NAME,
SPL.PRICE_LIST_ID,
RAR.RULE_ID INVOICING_RULE_ID,
RAR1.RULE_ID ACCOUNTING_RULE_ID,
OOL.TAX_VALUE,
OOL.UNIT_COST,
OOL.DELIVERY_LEAD_TIME,
OOL.REQUEST_ID,
OOL.REFERENCE_TYPE,
OOL.REFERENCE_HEADER_ID,
OOL.REFERENCE_LINE_ID,
OOL.DEMAND_CLASS_CODE,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
HAO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
HCA1.CUST_ACCOUNT_ID,
OOL.OPEN_FLAG,
OOL.BOOKED_FLAG
FROM OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
MTL_SYSTEM_ITEMS_B MSI,
OE_ORDER_SOURCES OOS,
RA_SALESREPS_ALL RSA,
OE_TRANSACTION_TYPES_ALL OTT,
RA_TERMS_B RTB,
RA_TERMS_TL RTT,
RA_RULES RAR,
RA_RULES RAR1,
SO_PRICE_LISTS_B SPL,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HR_ALL_ORGANIZATION_UNITS HAO,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1
WHERE OOH.HEADER_ID=OOL.HEADER_ID
AND OOL.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND OOL.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOL.SALESREP_ID=RSA.SALESREP_ID
AND OTT.TRANSACTION_TYPE_ID=OOH.ORDER_TYPE_ID
AND OOL.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOL.PAYMENT_TERM_ID=RTT.TERM_ID
AND OOL.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOL.INVOICING_RULE_ID=RAR.RULE_ID
AND OOL.ACCOUNTING_RULE_ID=RAR1.RULE_ID
AND OOL.SOLD_FROM_ORG_ID=HAO.ORGANIZATION_ID
AND OOL.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOL.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND OOL.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOL.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND OOL.HEADER_ID='94119'
AND MSI.ORGANIZATION_ID='204'
AND RSA.ORG_ID='204';
===========================================================================

CREATE OR REPLACE PACKAGE XX_OM_ORDER_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE XX_OM_STG_PROC;
PROCEDURE XX_OM_VALIDATE;
PROCEDURE XX_OM_LOAD;
END XX_OM_ORDER_PKG;

===========================================================================

CREATE OR REPLACE PACKAGE BODY XX_OM_ORDER_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
XX_OM_STG_PROC;
XX_OM_VALIDATE;
XX_OM_LOAD ;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END MAIN;
PROCEDURE XX_OM_STG_PROC
AS
CURSOR CUR_ORD_HEADER IS
SELECT OOH.HEADER_ID,
MSO.SEGMENT1,
OOS.ORDER_SOURCE_ID,
OOH.ORIG_SYS_DOCUMENT_REF,
OTT.TRANSACTION_TYPE_ID,
OOH.VERSION_NUMBER,
OOH.ORG_ID,
OOH.ORDERED_DATE,
HRO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
OOH.SOLD_TO_ORG_ID,
OOH.DELIVER_TO_ORG_ID,
OOH.SOLD_TO_CONTACT_ID,
RSA.SALESREP_ID,
RSA.NAME,
OOH.TRANSACTIONAL_CURR_CODE,
HCA.ACCOUNT_NAME,
HCA.ACCOUNT_NUMBER,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.SHIPPING_METHOD_CODE,
OTT.FOB_POINT_CODE,
SPL.PRICE_LIST_ID,
SPL.FREIGHT_TERMS_CODE,
SPL.DESCRIPTION,
OOH.CONVERSION_RATE,
OOH.CONVERSION_TYPE_CODE,
OOH.TAX_EXEMPT_REASON_CODE,
OOH.BOOKED_FLAG,
OOH.BOOKED_DATE,
OOH.OPEN_FLAG,
RAR.RULE_ID,
RTB.TERM_ID,
OOH.SALES_CHANNEL_CODE,
OOH.ORDER_CATEGORY_CODE,
OOH.REQUEST_ID
FROM OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_SOURCES OOS,
MTL_SALES_ORDERS MSO,
OE_TRANSACTION_TYPES_ALL OTT,
HR_ALL_ORGANIZATION_UNITS HRO,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1,
SO_PRICE_LISTS_B SPL,
RA_SALESREPS_ALL RSA,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
RA_RULES RAR,
RA_TERMS_B RTB
WHERE OOH.ORDER_NUMBER=MSO.SEGMENT1
AND OOH.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
AND OOH.SOLD_FROM_ORG_ID=HRO.ORGANIZATION_ID
AND OOH.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOH.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND OOH.SALESREP_ID=RSA.SALESREP_ID
AND OOH.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOH.INVOICING_RULE_ID=RAR.RULE_ID
AND OOH.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOH.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND RSA.ORG_ID='204'
AND OOH.ORDER_NUMBER='56701';
CURSOR CUR_ORD_LINES(HEADER_ID varchar2) IS
SELECT OOL.HEADER_ID,
OOL.LINE_ID,
OOL.LINE_NUMBER,
OOL.LINE_TYPE_ID,
OOL.SHIPMENT_NUMBER,
OOL.ORG_ID,
OOS.ORDER_SOURCE_ID,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
OOL.ORIG_SYS_DOCUMENT_REF,
OOL.ORIG_SYS_LINE_REF,
OOL.ORIG_SYS_SHIPMENT_REF,
OOL.PRICING_QUANTITY,
OOL.PRICING_QUANTITY_UOM,
OOL.ORDERED_QUANTITY,
OOL.ORDER_QUANTITY_UOM,
OOL.SHIPPING_QUANTITY,
OOL.SHIPPING_QUANTITY_UOM,
RSA.SALESREP_ID,
RSA.NAME SNAME,
OOL.SOURCE_TYPE_CODE,
OTT.SHIPMENT_PRIORITY_CODE,
OTT.FREIGHT_TERMS_CODE,
OTT.FOB_POINT_CODE,
OOL.UNIT_SELLING_PRICE,
OOL.UNIT_LIST_PRICE,
RTB.TERM_ID,
RTT.NAME,
SPL.PRICE_LIST_ID,
RAR.RULE_ID INVOICING_RULE_ID,
RAR1.RULE_ID ACCOUNTING_RULE_ID,
OOL.TAX_VALUE,
OOL.UNIT_COST,
OOL.DELIVERY_LEAD_TIME,
OOL.REQUEST_ID,
OOL.REFERENCE_TYPE,
OOL.REFERENCE_HEADER_ID,
OOL.REFERENCE_LINE_ID,
OOL.DEMAND_CLASS_CODE,
HZP.ADDRESS1 SHIP_TO_ADDRESS,
HZP1.ADDRESS1 BILL_TO_ADDRESS,
HZP.CITY,
HAO.ORGANIZATION_ID SOLD_FROM_ORG_ID,
HCS.SITE_USE_ID SHIPTO,
HCS1.SITE_USE_ID BILLTO,
HCA1.CUST_ACCOUNT_ID,
OOL.OPEN_FLAG,
OOL.BOOKED_FLAG
FROM OE_ORDER_LINES_ALL OOL,
OE_ORDER_HEADERS_ALL OOH,
MTL_SYSTEM_ITEMS_B MSI,
OE_ORDER_SOURCES OOS,
RA_SALESREPS_ALL RSA,
OE_TRANSACTION_TYPES_ALL OTT,
RA_TERMS_B RTB,
RA_TERMS_TL RTT,
RA_RULES RAR,
RA_RULES RAR1,
SO_PRICE_LISTS_B SPL,
HZ_PARTIES HZP,
HZ_PARTIES HZP1,
HR_ALL_ORGANIZATION_UNITS HAO,
HZ_CUST_ACCOUNTS HCA,
HZ_CUST_ACCOUNTS HCA1,
HZ_CUST_SITE_USES_ALL HCS,
HZ_CUST_SITE_USES_ALL HCS1
WHERE OOH.HEADER_ID=OOL.HEADER_ID
AND OOL.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND OOL.ORDER_SOURCE_ID=OOS.ORDER_SOURCE_ID
AND OOL.SALESREP_ID=RSA.SALESREP_ID
AND OTT.TRANSACTION_TYPE_ID=OOH.ORDER_TYPE_ID
AND OOL.PAYMENT_TERM_ID=RTB.TERM_ID
AND OOL.PAYMENT_TERM_ID=RTT.TERM_ID
AND OOL.PRICE_LIST_ID=SPL.PRICE_LIST_ID
AND OOL.INVOICING_RULE_ID=RAR.RULE_ID
AND OOL.ACCOUNTING_RULE_ID=RAR1.RULE_ID
AND OOL.SOLD_FROM_ORG_ID=HAO.ORGANIZATION_ID
AND OOL.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID
AND OOL.SOLD_TO_ORG_ID=HCA1.CUST_ACCOUNT_ID
AND OOL.SHIP_TO_ORG_ID=HCS.SITE_USE_ID
AND OOL.INVOICE_TO_ORG_ID=HCS1.SITE_USE_ID
AND HCA.PARTY_ID=HZP.PARTY_ID
AND HCA1.PARTY_ID=HZP1.PARTY_ID
AND OOL.HEADER_ID='94119'
AND MSI.ORGANIZATION_ID='204'
AND RSA.ORG_ID='204';
BEGIN
DELETE XX_HEADER_STG;
DELETE XX_LINE_STG;
COMMIT;
FOR REC_CUR_ORD_HEADER IN CUR_ORD_HEADER LOOP
INSERT INTO XX_HEADER_STG
(HEADER_ID,
ORDER_NUMBER,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
VERSION_NUMBER,
ORG_ID,
CUSTOMER_NUMBER,
ORDERED_DATE,
ORDER_TYPE_ID,
SALESREP_ID,
NAME,
PRICE_LIST_ID,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
TAX_EXEMPT_REASON_CODE,
BOOKED_FLAG,
BOOKED_DATE,
OPEN_FLAG,
TRANSACTIONAL_CURR_CODE,
INVOICING_RULE_ID,
PAYMENT_TERM_ID,
PARTY_NAME,
SHIPMENT_PRIORITY_CODE,
SHIPPING_METHOD_CODE,
FOB_POINT_CODE,
FREIGHT_TERMS_CODE,
DESCRIPTION,
SHIP_TO_ADDRESS,
BILL_TO_ADDRESS,
CITY,
SHIPTO,
BILLTO,
SOLD_TO_CONTACT_ID,
SALES_CHANNEL_CODE,
ORDER_CATEGORY_CODE,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
DELIVER_TO_ORG_ID,
REQUEST_ID,
STATUS_FLAG)
VALUES
(REC_CUR_ORD_HEADER.HEADER_ID,
REC_CUR_ORD_HEADER.SEGMENT1,
REC_CUR_ORD_HEADER.ORDER_SOURCE_ID,
REC_CUR_ORD_HEADER.ORIG_SYS_DOCUMENT_REF,
REC_CUR_ORD_HEADER.VERSION_NUMBER,
REC_CUR_ORD_HEADER.ORG_ID,
REC_CUR_ORD_HEADER.ACCOUNT_NUMBER,
REC_CUR_ORD_HEADER.ORDERED_DATE,
REC_CUR_ORD_HEADER.TRANSACTION_TYPE_ID,
REC_CUR_ORD_HEADER.SALESREP_ID,
REC_CUR_ORD_HEADER.NAME,
REC_CUR_ORD_HEADER.PRICE_LIST_ID,
REC_CUR_ORD_HEADER.CONVERSION_RATE,
REC_CUR_ORD_HEADER.CONVERSION_TYPE_CODE,
REC_CUR_ORD_HEADER.TAX_EXEMPT_REASON_CODE,
REC_CUR_ORD_HEADER.BOOKED_FLAG,
REC_CUR_ORD_HEADER.BOOKED_DATE,
REC_CUR_ORD_HEADER.OPEN_FLAG,
REC_CUR_ORD_HEADER.TRANSACTIONAL_CURR_CODE,
REC_CUR_ORD_HEADER.RULE_ID,
REC_CUR_ORD_HEADER.TERM_ID,
REC_CUR_ORD_HEADER.ACCOUNT_NAME,
REC_CUR_ORD_HEADER.SHIPMENT_PRIORITY_CODE,
REC_CUR_ORD_HEADER.SHIPPING_METHOD_CODE,
REC_CUR_ORD_HEADER.FOB_POINT_CODE,
REC_CUR_ORD_HEADER.FREIGHT_TERMS_CODE,
REC_CUR_ORD_HEADER.DESCRIPTION,
REC_CUR_ORD_HEADER.SHIP_TO_ADDRESS,
REC_CUR_ORD_HEADER.BILL_TO_ADDRESS,
REC_CUR_ORD_HEADER.CITY,
REC_CUR_ORD_HEADER.SHIPTO,
REC_CUR_ORD_HEADER.BILLTO,
REC_CUR_ORD_HEADER.SOLD_TO_CONTACT_ID,
REC_CUR_ORD_HEADER.SALES_CHANNEL_CODE,
REC_CUR_ORD_HEADER.ORDER_CATEGORY_CODE,
REC_CUR_ORD_HEADER.SOLD_FROM_ORG_ID,
REC_CUR_ORD_HEADER.SOLD_TO_ORG_ID,
REC_CUR_ORD_HEADER.DELIVER_TO_ORG_ID,
REC_CUR_ORD_HEADER.REQUEST_ID,
'N');
FOR REC_CUR_ORD_LINES IN CUR_ORD_LINES(REC_CUR_ORD_HEADER.HEADER_ID) LOOP
INSERT INTO XX_LINE_STG
(HEADER_ID,
LINE_ID,
LINE_TYPE_ID,
LINE_NUMBER,
ORG_ID,
SEGMENT1,
PRICING_QUANTITY,
PRICING_QUANTITY_UOM,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
SHIPPING_QUANTITY,
SHIPPING_QUANTITY_UOM,
SHIPMENT_NUMBER,
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORIG_SYS_SHIPMENT_REF,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SALESREP_ID,
SNAME,
ACCOUNTING_RULE_ID,
SOURCE_TYPE_CODE,
SHIPMENT_PRIORITY_CODE,
FREIGHT_TERMS_CODE,
FOB_POINT_CODE,
UNIT_SELLING_PRICE,
UNIT_LIST_PRICE,
PAYMENT_TERM_ID,
NAME,
PRICE_LIST_ID,
INVOICING_RULE_ID,
TAX_VALUE,
UNIT_COST,
DELIVERY_LEAD_TIME,
SHIP_TO_ADDRESS,
BILL_TO_ADDRESS,
CITY,
SHIPTO,
BILLTO,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
BOOKED_FLAG,
OPEN_FLAG,
REQUEST_ID,
REFERENCE_TYPE ,
REFERENCE_HEADER_ID,
REFERENCE_LINE_ID,
DEMAND_CLASS_CODE,
STATUS_FLAG)
VALUES
(REC_CUR_ORD_LINES.HEADER_ID,
REC_CUR_ORD_LINES.LINE_ID,
REC_CUR_ORD_LINES.LINE_TYPE_ID,
REC_CUR_ORD_LINES.LINE_NUMBER,
REC_CUR_ORD_LINES.ORG_ID,
REC_CUR_ORD_LINES.SEGMENT1,
REC_CUR_ORD_LINES.PRICING_QUANTITY,
REC_CUR_ORD_LINES.PRICING_QUANTITY_UOM,
REC_CUR_ORD_LINES.ORDERED_QUANTITY,
REC_CUR_ORD_LINES.ORDER_QUANTITY_UOM,
REC_CUR_ORD_LINES.SHIPPING_QUANTITY,
REC_CUR_ORD_LINES.SHIPPING_QUANTITY_UOM,
REC_CUR_ORD_LINES.SHIPMENT_NUMBER,
REC_CUR_ORD_LINES.ORDER_SOURCE_ID,
REC_CUR_ORD_LINES.ORIG_SYS_DOCUMENT_REF,
REC_CUR_ORD_LINES.ORIG_SYS_LINE_REF,
REC_CUR_ORD_LINES.ORIG_SYS_SHIPMENT_REF,
REC_CUR_ORD_LINES.INVENTORY_ITEM_ID,
REC_CUR_ORD_LINES.ORGANIZATION_ID,
REC_CUR_ORD_LINES.SALESREP_ID,
REC_CUR_ORD_LINES.SNAME,
REC_CUR_ORD_LINES.ACCOUNTING_RULE_ID,
REC_CUR_ORD_LINES.SOURCE_TYPE_CODE,
REC_CUR_ORD_LINES.SHIPMENT_PRIORITY_CODE,
REC_CUR_ORD_LINES.FREIGHT_TERMS_CODE,
REC_CUR_ORD_LINES.FOB_POINT_CODE,
REC_CUR_ORD_LINES.UNIT_SELLING_PRICE,
REC_CUR_ORD_LINES.UNIT_LIST_PRICE,
REC_CUR_ORD_LINES.TERM_ID,
REC_CUR_ORD_LINES.NAME,
REC_CUR_ORD_LINES.PRICE_LIST_ID,
REC_CUR_ORD_LINES.INVOICING_RULE_ID,
REC_CUR_ORD_LINES.TAX_VALUE,
REC_CUR_ORD_LINES.UNIT_COST,
REC_CUR_ORD_LINES.DELIVERY_LEAD_TIME,
REC_CUR_ORD_LINES.SHIP_TO_ADDRESS,
REC_CUR_ORD_LINES.BILL_TO_ADDRESS,
REC_CUR_ORD_LINES.CITY,
REC_CUR_ORD_LINES.SHIPTO,
REC_CUR_ORD_LINES.BILLTO,
REC_CUR_ORD_LINES.SOLD_FROM_ORG_ID,
REC_CUR_ORD_LINES.CUST_ACCOUNT_ID,
REC_CUR_ORD_LINES.BOOKED_FLAG,
REC_CUR_ORD_LINES.OPEN_FLAG,
REC_CUR_ORD_LINES.REQUEST_ID,
REC_CUR_ORD_LINES.REFERENCE_TYPE,
REC_CUR_ORD_LINES.REFERENCE_HEADER_ID,
REC_CUR_ORD_LINES.REFERENCE_LINE_ID,
REC_CUR_ORD_LINES.DEMAND_CLASS_CODE,
'N');
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END XX_OM_STG_PROC;
PROCEDURE XX_OM_VALIDATE
IS
L_CUSTOMER_NUMBER HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
L_CUSTOMER_NAME HZ_CUST_ACCOUNTS.ACCOUNT_NAME%TYPE;
L_SALESREP_ID RA_SALESREPS_ALL.SALESREP_ID%TYPE;
L_NAME RA_SALESREPS_ALL.NAME%TYPE;
L_SNAME RA_SALESREPS_ALL.NAME%TYPE;
L_PRICE_LIST_ID SO_PRICE_LISTS_B.PRICE_LIST_ID%TYPE;
L_PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE;
L_FREIGHT_TERMS_CODE OE_ORDER_HEADERS_ALL.FREIGHT_TERMS_CODE%TYPE;
L_CURRENCY_CODE FND_CURRENCIES.CURRENCY_CODE%TYPE;
L_BILLTO HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE;
L_SHIPTO HZ_CUST_SITE_USES_ALL.SITE_USE_ID%TYPE;
L_SOLD_FROM_ORG_ID HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_ORGANIZATION_CODE MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
L_ACCOUNTING_RULE_ID RA_RULES.RULE_ID%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000):=NULL;
CURSOR CUR_VALID_OM IS SELECT A.*,ROWID FROM XX_HEADER_STG A WHERE STATUS_FLAG='N';
CURSOR CUR_VALID_OM1(HEADER_ID NUMBER) IS SELECT B.*,ROWID FROM XX_LINE_STG B WHERE
STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VALID_OM IN CUR_VALID_OM LOOP
BEGIN
SELECT ACCOUNT_NUMBER INTO L_CUSTOMER_NUMBER
FROM HZ_CUST_ACCOUNTS
WHERE ACCOUNT_NUMBER=REC_CUR_VALID_OM.CUSTOMER_NUMBER;
UPDATE XX_HEADER_STG
SET CUSTOMER_NUMBER=L_CUSTOMER_NUMBER
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'CUSTOMER NUMBER DOES NOT EXIST';
END;
BEGIN
SELECT ACCOUNT_NAME INTO L_CUSTOMER_NUMBER
FROM HZ_CUST_ACCOUNTS
WHERE ACCOUNT_NAME=REC_CUR_VALID_OM.PARTY_NAME;
UPDATE XX_HEADER_STG
SET PARTY_NAME=L_CUSTOMER_NAME
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'CUSTOMER NAME DOES NOT EXIST';
END;
BEGIN
SELECT SALESREP_ID INTO L_SALESREP_ID
FROM RA_SALESREPS_ALL
WHERE SALESREP_ID=REC_CUR_VALID_OM.SALESREP_ID
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_HEADER_STG
SET SALESREP_ID=L_SALESREP_ID
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALESREP ID DOES NOT EXIST';
END;
BEGIN
SELECT NAME INTO L_NAME
FROM RA_SALESREPS_ALL
WHERE NAME=REC_CUR_VALID_OM.NAME
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_HEADER_STG
SET NAME=L_NAME
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALES NAME DOES NOT EXIST';
END;
BEGIN
SELECT PRICE_LIST_ID INTO L_PRICE_LIST_ID
FROM SO_PRICE_LISTS_B
WHERE PRICE_LIST_ID=REC_CUR_VALID_OM.PRICE_LIST_ID;
UPDATE XX_HEADER_STG
SET PRICE_LIST_ID=L_PRICE_LIST_ID
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'PRICE LIST ID DOES NOT EXIST';
END;
BEGIN
SELECT PARTY_NAME INTO L_PARTY_NAME
FROM HZ_PARTIES
WHERE PARTY_NAME=REC_CUR_VALID_OM.PARTY_NAME;
UPDATE XX_HEADER_STG
SET PARTY_NAME=L_PARTY_NAME
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'PARTY NAME DOES NOT EXIST';
END;
BEGIN
SELECT LOOKUP_CODE INTO L_FREIGHT_TERMS_CODE
FROM FND_LOOKUP_VALUES FLT,FND_APPLICATION_TL FAT
WHERE FLT.VIEW_APPLICATION_ID=FAT.APPLICATION_ID
AND FLT.VIEW_APPLICATION_ID='660'
AND FLT.LOOKUP_CODE=REC_CUR_VALID_OM.FREIGHT_TERMS_CODE;
UPDATE XX_HEADER_STG
SET FREIGHT_TERMS_CODE=L_FREIGHT_TERMS_CODE
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'FREIGHT TERMS CODE DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_BILLTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM.BILLTO;
UPDATE XX_HEADER_STG
SET BILLTO=L_BILLTO
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SITE USE ID DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_SHIPTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM.SHIPTO;
UPDATE XX_HEADER_STG
SET SHIPTO=L_SHIPTO
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SITE USE ID DOES NOT EXIST';
END;
BEGIN
SELECT ORGANIZATION_ID INTO L_SOLD_FROM_ORG_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID=REC_CUR_VALID_OM.SOLD_FROM_ORG_ID;
UPDATE XX_HEADER_STG
SET SOLD_FROM_ORG_ID=L_SOLD_FROM_ORG_ID
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOLD FROM ORG ID DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_HEADER_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
ELSE
UPDATE XX_HEADER_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VALID_OM.ROWID;
END IF;
FOR REC_CUR_VALID_OM1 IN CUR_VALID_OM1(REC_CUR_VALID_OM.HEADER_ID) LOOP
L_ERROR_MESSAGE:=NULL;
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
L_PRICE_LIST_ID:=NULL;
L_SOLD_FROM_ORG_ID:=NULL;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = REC_CUR_VALID_OM1.ORGANIZATION_ID;
UPDATE XX_LINE_STG
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ORGANIZATION ID DOES NOT EXIST';
END;
BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=REC_CUR_VALID_OM1.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= REC_CUR_VALID_OM1.ORGANIZATION_ID;
UPDATE XX_LINE_STG
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT LOOKUP_CODE INTO L_FREIGHT_TERMS_CODE
FROM FND_LOOKUP_VALUES FLT,FND_APPLICATION_TL FAT
WHERE FLT.VIEW_APPLICATION_ID=FAT.APPLICATION_ID
AND FLT.VIEW_APPLICATION_ID='660'
AND FLT.LOOKUP_CODE=REC_CUR_VALID_OM1.FREIGHT_TERMS_CODE;
UPDATE XX_LINE_STG
SET FREIGHT_TERMS_CODE=L_FREIGHT_TERMS_CODE
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'FREIGHT TERMS CODE DOES NOT EXIST';
END;
BEGIN
SELECT SALESREP_ID INTO L_SALESREP_ID
FROM RA_SALESREPS_ALL
WHERE SALESREP_ID=REC_CUR_VALID_OM1.SALESREP_ID
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_LINE_STG
SET SALESREP_ID=L_SALESREP_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALESREP ID DOES NOT EXIST';
END;
BEGIN
SELECT NAME INTO L_SNAME
FROM RA_SALESREPS_ALL
WHERE NAME=REC_CUR_VALID_OM1.SNAME
AND ORG_ID=REC_CUR_VALID_OM.ORG_ID;
UPDATE XX_LINE_STG
SET SNAME=L_SNAME
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SALES NAME DOES NOT EXIST';
END;
BEGIN
SELECT PRICE_LIST_ID INTO L_PRICE_LIST_ID
FROM SO_PRICE_LISTS_B
WHERE PRICE_LIST_ID=REC_CUR_VALID_OM1.PRICE_LIST_ID;
UPDATE XX_LINE_STG
SET PRICE_LIST_ID=L_PRICE_LIST_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'PRICE LIST ID DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_BILLTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM1.BILLTO;
UPDATE XX_LINE_STG
SET BILLTO=L_BILLTO
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'BILL TO SITE ID DOES NOT EXIST';
END;
BEGIN
SELECT SITE_USE_ID INTO L_SHIPTO
FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_ID=REC_CUR_VALID_OM1.SHIPTO;
UPDATE XX_LINE_STG
SET SHIPTO=L_SHIPTO
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SHIP TO SITE ID DOES NOT EXIST';
END;
BEGIN
SELECT ORGANIZATION_ID INTO L_SOLD_FROM_ORG_ID
FROM HR_ALL_ORGANIZATION_UNITS
WHERE ORGANIZATION_ID=REC_CUR_VALID_OM1.SOLD_FROM_ORG_ID;
UPDATE XX_LINE_STG
SET SOLD_FROM_ORG_ID=L_SOLD_FROM_ORG_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOLD FROM ORG ID DOES NOT EXIST';
END;
BEGIN
SELECT RULE_ID INTO L_ACCOUNTING_RULE_ID
FROM RA_RULES
WHERE RULE_ID=REC_CUR_VALID_OM1.ACCOUNTING_RULE_ID;
UPDATE XX_LINE_STG
SET ACCOUNTING_RULE_ID=L_ACCOUNTING_RULE_ID
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ACCOUNTING RULE ID DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_LINE_STG
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
ELSE
UPDATE XX_LINE_STG
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VALID_OM1.ROWID;
END IF;
END LOOP;
COMMIT;
END LOOP;
END XX_OM_VALIDATE;
PROCEDURE XX_OM_LOAD
AS
L_CREATED_BY NUMBER;
L_UPDATED_BY NUMBER;
L_CREATION_DATE DATE := SYSDATE;
L_LAST_UPDATE_DATE DATE := SYSDATE;
L_USER NUMBER;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_REC NUMBER := 0;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VALID1 is select A.*,ROWID from XX_HEADER_STG A WHERE A.STATUS_FLAG='P';
CURSOR CUR_VALID2 (HEADER_ID NUMBER) IS select B.*,ROWID from XX_LINE_STG B
WHERE B.HEADER_ID=HEADER_ID;
BEGIN
SELECT USER_ID
INTO L_USER
FROM FND_USER
WHERE UPPER (USER_NAME) = 'OPERATIONS';
DELETE OE_HEADERS_IFACE_ALL;
DELETE OE_LINES_IFACE_ALL;
COMMIT;
FOR REC_CUR_VALID1 IN CUR_VALID1 LOOP
L_ERROR_MESSAGE:=NULL;
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORG_ID,
ORDER_TYPE_ID,
HEADER_ID,
PRICE_LIST_ID,
CONVERSION_RATE,
CONVERSION_TYPE_CODE,
TAX_EXEMPT_REASON_CODE,
SALESREP_ID,
INVOICING_RULE_ID,
PAYMENT_TERM_ID,
SHIPMENT_PRIORITY_CODE,
SHIPPING_METHOD_CODE,
FREIGHT_TERMS_CODE,
FOB_POINT_CODE,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
DELIVER_TO_ORG_ID,
SOLD_TO_CONTACT_ID,
CUSTOMER_NAME,
SALES_CHANNEL_CODE,
BOOKED_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OPERATION_CODE
)
VALUES
( '1046',
REC_CUR_VALID1.ORIG_SYS_DOCUMENT_REF,
REC_CUR_VALID1.ORG_ID,
REC_CUR_VALID1.ORDER_TYPE_ID,
OE_ORDER_HEADERS_S.NEXTVAL,
REC_CUR_VALID1.PRICE_LIST_ID,
REC_CUR_VALID1.CONVERSION_RATE,
REC_CUR_VALID1.CONVERSION_TYPE_CODE,
REC_CUR_VALID1.TAX_EXEMPT_REASON_CODE,
REC_CUR_VALID1.SALESREP_ID,
REC_CUR_VALID1.INVOICING_RULE_ID,
REC_CUR_VALID1.PAYMENT_TERM_ID,
REC_CUR_VALID1.SHIPMENT_PRIORITY_CODE,
REC_CUR_VALID1.SHIPPING_METHOD_CODE,
REC_CUR_VALID1.FREIGHT_TERMS_CODE,
REC_CUR_VALID1.FOB_POINT_CODE,
REC_CUR_VALID1.SOLD_FROM_ORG_ID,
REC_CUR_VALID1.SOLD_TO_ORG_ID,
REC_CUR_VALID1.SHIPTO,
REC_CUR_VALID1.BILLTO,
REC_CUR_VALID1.DELIVER_TO_ORG_ID,
REC_CUR_VALID1.SOLD_TO_CONTACT_ID,
REC_CUR_VALID1.PARTY_NAME,
REC_CUR_VALID1.SALES_CHANNEL_CODE,
REC_CUR_VALID1.BOOKED_FLAG,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
'INSERT'
);
FOR REC_CUR_VALID2 IN CUR_VALID2( REC_CUR_VALID1.HEADER_ID) LOOP
INSERT INTO OE_LINES_IFACE_ALL
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
LINE_ID,
ORG_ID,
INVENTORY_ITEM,
SOURCE_TYPE_CODE,
REQUEST_DATE,
DELIVERY_LEAD_TIME,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
SHIPPING_QUANTITY,
SHIPPING_QUANTITY_UOM,
PRICING_QUANTITY,
PRICING_QUANTITY_UOM,
SOLD_FROM_ORG_ID,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
PRICE_LIST_ID,
REFERENCE_TYPE,
REFERENCE_HEADER_ID,
REFERENCE_LINE_ID,
UNIT_SELLING_PRICE,
TAX_VALUE,
INVOICING_RULE_ID,
PAYMENT_TERM_ID,
SHIPMENT_PRIORITY_CODE,
FREIGHT_TERMS_CODE,
FOB_POINT_CODE,
SALESREP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DEMAND_CLASS_CODE)
VALUES
('1046',
REC_CUR_VALID2.ORIG_SYS_DOCUMENT_REF,
REC_CUR_VALID2.ORIG_SYS_LINE_REF,
OE_ORDER_LINES_S.NEXTVAL,
REC_CUR_VALID2.ORG_ID,
'AS54999',
REC_CUR_VALID2.SOURCE_TYPE_CODE,
SYSDATE,
REC_CUR_VALID2.DELIVERY_LEAD_TIME,
REC_CUR_VALID2.ORDERED_QUANTITY,
REC_CUR_VALID2.ORDER_QUANTITY_UOM,
REC_CUR_VALID2.SHIPPING_QUANTITY,
REC_CUR_VALID2.SHIPPING_QUANTITY_UOM,
REC_CUR_VALID2.PRICING_QUANTITY,
REC_CUR_VALID2.PRICING_QUANTITY_UOM,
REC_CUR_VALID2.SOLD_FROM_ORG_ID,
REC_CUR_VALID2.SOLD_TO_ORG_ID,
REC_CUR_VALID2.SHIPTO,
REC_CUR_VALID2.BILLTO,
REC_CUR_VALID2.PRICE_LIST_ID,
REC_CUR_VALID2.REFERENCE_TYPE,
REC_CUR_VALID2.REFERENCE_HEADER_ID,
REC_CUR_VALID2.REFERENCE_LINE_ID,
REC_CUR_VALID2.UNIT_SELLING_PRICE,
REC_CUR_VALID2.TAX_VALUE,
REC_CUR_VALID2.INVOICING_RULE_ID,
REC_CUR_VALID2.PAYMENT_TERM_ID,
REC_CUR_VALID2.SHIPMENT_PRIORITY_CODE,
REC_CUR_VALID2.FREIGHT_TERMS_CODE,
REC_CUR_VALID2.FOB_POINT_CODE,
REC_CUR_VALID2.SALESREP_ID,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
'-1',
REC_CUR_VALID2.DEMAND_CLASS_CODE);
END LOOP;
COMMIT;
END LOOP;
COMMIT;
END XX_OM_LOAD;
END XX_OM_ORDER_PKG;

=======================================================================

RUN THE IMPORT PROGRAM:
=======================

1)ORDER IMPORT STATISTICS
2)ORDER IMPORT

========================================================================

OM Work Flow

Package name : OE_CLOSE_WF
Procedure name : CLOSE_LINE

====================================================CREATE OR REPLACE PACKAGE BODY OE_Close_WF as

PROCEDURE Close_Line(
itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
resultout in out varchar2)
IS
L_HEADER_ID NUMBER;
l_line_id NUMBER;
l_return_status VARCHAR2(30);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
BEGIN

if (funcmode = 'RUN') then

OE_STANDARD_WF.Set_Msg_Context(actid);

l_line_id := to_number(itemkey);

SELECT HEADER_ID INTO L_HEADER_ID FROM OE_ORDER_LINES_ALL WHERE LINE_ID=L_LINE_ID;

OE_ORDER_CLOSE_UTIL.Close_Line
( p_api_version_number => 1.0
, p_line_id => l_line_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);
OE_ORDER_CLOSE_UTIL.Close_Order
( p_api_version_number => 1.0
, p_header_id => l_header_id
, x_return_status => l_return_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
);

IF l_return_status = FND_API.G_RET_STS_ERROR THEN
resultout := 'COMPLETE:INCOMPLETE';
OE_STANDARD_WF.Save_Messages;
OE_STANDARD_WF.Clear_Msg_Context;
return;
ELSIF l_return_status = 'H' THEN
resultout := 'COMPLETE:ON_HOLD';
OE_STANDARD_WF.Save_Messages;
OE_STANDARD_WF.Clear_Msg_Context;
return;
ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
-- start data fix project
-- OE_STANDARD_WF.Save_Messages;
-- OE_STANDARD_WF.Clear_Msg_Context;
-- end data fix project
app_exception.raise_exception;
END IF;

resultout := 'COMPLETE:COMPLETE';
OE_STANDARD_WF.Clear_Msg_Context;
return;

end if; -- End for 'RUN' mode

--
-- CANCEL mode - activity 'compensation'
--
-- This is an event point is called with the effect of the activity must
-- be undone, for example when a process is reset to an earlier point
-- due to a loop back.
--
if (funcmode = 'CANCEL') then

-- your cancel code goes here
null;

-- no result needed
resultout := 'COMPLETE';
return;
end if;


--
-- Other execution modes may be created in the future. Your
-- activity will indicate that it does not implement a mode
-- by returning null
--
-- resultout := '';
-- return;

exception
when others then
-- The line below records this function call in the error system
-- in the case of an exception.
wf_core.context('OE_Close_WF', 'Close_Line',
itemtype, itemkey, to_char(actid), funcmode);
-- start data fix project
OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => actid,
p_itemtype => itemtype,
p_itemkey => itemkey);
OE_STANDARD_WF.Save_Messages;
OE_STANDARD_WF.Clear_Msg_Context;
-- end data fix project
raise;
END Close_Line;

END OE_Close_WF;

Interface to process Item Pending Statuses

CREATE OR REPLACE PACKAGE XX_PEND_STATUS_PKG AS
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE XX_PEND_ITEM_STG;
PROCEDURE XX_PEND_VAL_STG;
PROCEDURE XX_PROCESS_PEND;
END XX_PEND_STATUS_PKG;


CREATE OR REPLACE PACKAGE BODY XX_PEND_STATUS_PKG AS
L_ERROR_MESSAGE VARCHAR2(1000);
PROCEDURE MAIN(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)
AS
BEGIN
XX_PEND_ITEM_STG;
XX_PEND_VAL_STG;
XX_PROCESS_PEND;
EXCEPTION
WHEN OTHERS
THEN
RAISE_APPLICATION_ERROR(-20000,SQLERRM);
END MAIN;

PROCEDURE XX_PEND_ITEM_STG IS
CURSOR CUR_PEND_ITEM IS
SELECT MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MP.ORGANIZATION_ID,
MP.ORGANIZATION_CODE,
MIS.INVENTORY_ITEM_STATUS_CODE,
MIS.DESCRIPTION,
MPI.IMPLEMENTED_DATE,
MPI.EFFECTIVE_DATE,
MPI.PENDING_FLAG
FROM MTL_SYSTEM_ITEMS_B MSI,
MTL_PENDING_ITEM_STATUS MPI,
MTL_PARAMETERS MP,
MTL_ITEM_STATUS MIS
WHERE MSI.INVENTORY_ITEM_ID=MPI.INVENTORY_ITEM_ID
AND MP.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MPI.ORGANIZATION_ID=MP.ORGANIZATION_ID
AND MPI.STATUS_CODE=MIS.INVENTORY_ITEM_STATUS_CODE
AND MSI.INVENTORY_ITEM_ID='8063'
AND MP.ORGANIZATION_ID='204'
AND MPI.PENDING_FLAG='Y';
BEGIN
DELETE XX_PENDING_ITEM_STATUS;
COMMIT;
FOR REC_CUR_PEND_ITEM IN CUR_PEND_ITEM LOOP
INSERT INTO XX_PENDING_ITEM_STATUS
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
STATUS_CODE,
IMPLEMENTED_DATE,
PENDING_FLAG,
EFFECTIVE_DATE,
STATUS_FLAG)
VALUES
(REC_CUR_PEND_ITEM.INVENTORY_ITEM_ID,
REC_CUR_PEND_ITEM.ORGANIZATION_ID,
REC_CUR_PEND_ITEM.INVENTORY_ITEM_STATUS_CODE,
REC_CUR_PEND_ITEM.IMPLEMENTED_DATE,
REC_CUR_PEND_ITEM.PENDING_FLAG,
REC_CUR_PEND_ITEM.EFFECTIVE_DATE,
'N');
END LOOP;
END XX_PEND_ITEM_STG;

PROCEDURE XX_PEND_VAL_STG
IS
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_ORGANIZATION_CODE MTL_PARAMETERS.ORGANIZATION_CODE%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_INVENTORY_ITEM_STATUS_CODE MTL_ITEM_STATUS.INVENTORY_ITEM_STATUS_CODE%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_PEND_VALID IS
SELECT A.*,ROWID
FROM XX_PENDING_ITEM_STATUS A WHERE A.STATUS_FLAG='N';
BEGIN
FOR REC_CUR_PEND_VALID IN CUR_PEND_VALID LOOP
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = REC_CUR_PEND_VALID.ORGANIZATION_ID;
UPDATE XX_PENDING_ITEM_STATUS
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_PEND_VALID.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ORGANIZATION ID DOES NOT EXIST';
END;
BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=REC_CUR_PEND_VALID.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= L_ORGANIZATION_ID;
UPDATE XX_PENDING_ITEM_STATUS
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT INVENTORY_ITEM_STATUS_CODE
INTO L_INVENTORY_ITEM_STATUS_CODE
FROM MTL_ITEM_STATUS
WHERE INVENTORY_ITEM_STATUS_CODE=REC_CUR_PEND_VALID.STATUS_CODE;
UPDATE XX_PENDING_ITEM_STATUS
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM STATUS CODE DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_PENDING_ITEM_STATUS
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
ELSE
UPDATE XX_PENDING_ITEM_STATUS
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_PEND_VALID.ROWID;
END IF;
END LOOP;
END XX_PEND_VAL_STG;

PROCEDURE XX_PROCESS_PEND AS
A VARCHAR2(1000);
B NUMBER;
BEGIN
BEGIN
FND_CLIENT_INFO.SET_ORG_CONTEXT('204');
END;
INV_ITEM_STATUS_CP.PROCESS_PENDING_STATUS(A,B,p_Org_Id => NULL,p_Item_Id => NULL ,
p_commit => 'TRUE',p_prog_appid => NULL,p_request_id => NULL,p_user_id =>'1318',
p_login_id => '-1',p_init_msg_list => FND_API.G_TRUE,p_msg_logname => 'FILE');
END XX_PROCESS_PEND;
END XX_PEND_STATUS_PKG;

Interface to process Issues and Receipts of Items

CREATING A FLAT FILE AS DATA TO BE TRANSFERRED FROM EXTERNAL
=========================================================
WAREHOUSE MANAGEMENT SOFTWARE:
================================

CREATE OR REPLACE PROCEDURE EXT_MNG(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
CURSOR CUR_EXT_MNG IS SELECT WDD.SOURCE_CODE,
WDD.SOURCE_LINE_ID,
WDD.SOURCE_HEADER_ID,
MMT.TRANSACTION_MODE,
MMT.LAST_UPDATE_DATE,
MMT.LAST_UPDATED_BY,
MMT.CREATION_DATE,
MMT.CREATED_BY,
MMT.LAST_UPDATE_LOGIN,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_UOM,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_SOURCE_ID,
MTS.TRANSACTION_SOURCE_TYPE_ID,
MTT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_ID,
MTT.TRANSACTION_TYPE_NAME,
MTS.TRANSACTION_SOURCE_TYPE_NAME,
MTI.SECONDARY_INVENTORY_NAME,
MMT.ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_TXN_SOURCE_TYPES MTS,
WSH_DELIVERY_DETAILS WDD,
MTL_SECONDARY_INVENTORIES MTI
WHERE MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MSI.INVENTORY_ITEM_ID=WDD.INVENTORY_ITEM_ID(+)
AND MSI.ORGANIZATION_ID=WDD.ORGANIZATION_ID(+)
AND MMT.SUBINVENTORY_CODE=MTI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID=MTI.ORGANIZATION_ID
AND MSI.SEGMENT1='SRI';
R_ID UTL_FILE.FILE_TYPE;
BEGIN
R_ID:=UTL_FILE.FOPEN('C:\TEMP','EXT_MGT_SW.TXT','W');
FOR REC_CUR_EXT_MNG IN CUR_EXT_MNG LOOP
UTL_FILE.PUT_LINE(R_ID,REC_CUR_EXT_MNG.SOURCE_CODE || ',' ||
REC_CUR_EXT_MNG.SOURCE_LINE_ID || ',' ||
REC_CUR_EXT_MNG.SOURCE_HEADER_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_MODE || ',' ||
REC_CUR_EXT_MNG.LAST_UPDATE_DATE || ',' ||
REC_CUR_EXT_MNG.LAST_UPDATED_BY || ',' ||
REC_CUR_EXT_MNG.CREATION_DATE || ',' ||
REC_CUR_EXT_MNG.CREATED_BY || ',' ||
REC_CUR_EXT_MNG.LAST_UPDATE_LOGIN || ',' ||
REC_CUR_EXT_MNG.INVENTORY_ITEM_ID || ',' ||
REC_CUR_EXT_MNG.SEGMENT1 ||','||
REC_CUR_EXT_MNG.ORGANIZATION_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_QUANTITY || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_UOM || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_DATE || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_SOURCE_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_SOURCE_TYPE_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_ACTION_ID || ',' ||
REC_CUR_EXT_MNG.TRANSACTION_TYPE_ID ||','||
REC_CUR_EXT_MNG.TRANSACTION_TYPE_NAME ||','||
REC_CUR_EXT_MNG.TRANSACTION_SOURCE_TYPE_NAME ||','||
REC_CUR_EXT_MNG.SECONDARY_INVENTORY_NAME || ',' ||
REC_CUR_EXT_MNG.ACTUAL_COST);
END LOOP;
UTL_FILE.FCLOSE(R_ID);
END EXT_MNG;


===================================================

LOAD DATA INTO STAGING TABLE:
============================

LOAD DATA
INFILE 'E:\RAJASEKHAR\RAJA\PROJECT\ITEM ISSUES AND RECEIPTS\EXT_MGT_SW.TXT'
INSERT INTO TABLE XX_ITEM_ISS_REC
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
TRANSACTION_MODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SEGMENT1,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_TYPE_NAME,
TRANSACTION_SOURCE_TYPE_NAME,
SECONDARY_INVENTORY_NAME,
ACTUAL_COST);
=======================================================

=========================================================================
CREATING STAGING TABLE:
=======================
CREATE TABLE XX_ITEM_ISS_REC
(TRANSACTION_INTERFACE_ID NUMBER,
TRANSACTION_ID NUMBER,
SOURCE_CODE VARCHAR2(30),
SOURCE_LINE_ID NUMBER,
SOURCE_HEADER_ID NUMBER,
TRANSACTION_MODE NUMBER,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATION_DATE DATE,
CREATED_BY NUMBER,
LAST_UPDATE_LOGIN NUMBER,
INVENTORY_ITEM_ID NUMBER,
SEGMENT1 VARCHAR2(40),
ORGANIZATION_ID NUMBER,
TRANSACTION_QUANTITY NUMBER,
TRANSACTION_UOM VARCHAR2(3),
TRANSACTION_DATE DATE,
TRANSACTION_SOURCE_ID NUMBER,
TRANSACTION_SOURCE_TYPE_ID NUMBER,
TRANSACTION_ACTION_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_TYPE_NAME VARCHAR2(80),
TRANSACTION_SOURCE_TYPE_NAME VARCHAR2(30),
SECONDARY_INVENTORY_NAME VARCHAR2(10),
ACTUAL_COST NUMBER,
STATUS_FLAG VARCHAR2(1),
ERROR_MESSAGE VARCHAR2(1000));

========================================================================

SELECT MMT.SOURCE_CODE,
MMT.SOURCE_LINE_ID,
OOH.HEADER_ID,
MMT.TRANSACTION_MODE,
MMT.LAST_UPDATE_DATE,
MMT.CREATED_BY,
MMT.CREATION_DATE,
MMT.LAST_UPDATED_BY,
MMT.LAST_UPDATE_LOGIN,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_UOM,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_SOURCE_ID,
MTS.TRANSACTION_SOURCE_TYPE_ID,
MTT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_ID,
MTT.TRANSACTION_TYPE_NAME,
MTS.TRANSACTION_SOURCE_TYPE_NAME,
MTI.SECONDARY_INVENTORY_NAME,
MMT.ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_TXN_SOURCE_TYPES MTS,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
MTL_SECONDARY_INVENTORIES MTI
WHERE MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MMT.SOURCE_LINE_ID=OOL.LINE_ID
AND OOH.HEADER_ID=OOL.HEADER_ID
AND MMT.SUBINVENTORY_CODE=MTI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID=MTI.ORGANIZATION_ID
AND MMT.SOURCE_CODE IS NOT NULL
AND MMT.SOURCE_LINE_ID='196027'


=====================================================

CREATE OR REPLACE PROCEDURE P1(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
I NUMBER:=1;
CURSOR CUR_STG IS
SELECT MMT.TRANSACTION_ID,
MMT.SOURCE_CODE,
MMT.SOURCE_LINE_ID,
OOH.HEADER_ID,
MMT.TRANSACTION_MODE,
MMT.LAST_UPDATE_DATE,
MMT.LAST_UPDATED_BY,
MMT.CREATION_DATE,
MMT.CREATED_BY,
MMT.LAST_UPDATE_LOGIN,
MSI.INVENTORY_ITEM_ID,
MSI.SEGMENT1,
MSI.ORGANIZATION_ID,
MMT.TRANSACTION_QUANTITY,
MMT.TRANSACTION_UOM,
MMT.TRANSACTION_DATE,
MMT.TRANSACTION_SOURCE_ID,
MTS.TRANSACTION_SOURCE_TYPE_ID,
MTT.TRANSACTION_ACTION_ID,
MTT.TRANSACTION_TYPE_ID,
MTT.TRANSACTION_TYPE_NAME,
MTS.TRANSACTION_SOURCE_TYPE_NAME,
MTI.SECONDARY_INVENTORY_NAME,
MMT.ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_SYSTEM_ITEMS_B MSI,
MTL_TXN_SOURCE_TYPES MTS,
OE_ORDER_HEADERS_ALL OOH,
OE_ORDER_LINES_ALL OOL,
MTL_SECONDARY_INVENTORIES MTI
WHERE MMT.TRANSACTION_TYPE_ID=MTT.TRANSACTION_TYPE_ID
AND MMT.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=MSI.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID=MTS.TRANSACTION_SOURCE_TYPE_ID
AND MMT.SOURCE_LINE_ID=OOL.LINE_ID
AND OOH.HEADER_ID=OOL.HEADER_ID
AND MMT.SUBINVENTORY_CODE=MTI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID=MTI.ORGANIZATION_ID
AND MMT.SOURCE_CODE IS NOT NULL
AND MMT.SOURCE_LINE_ID='196027';
-- AND MMT.SOURCE_CODE='RCV'
BEGIN
DELETE XX_ITEM_ISS_REC;
COMMIT;
FOR REC_CUR_STG IN CUR_STG
LOOP
INSERT INTO XX_ITEM_ISS_REC
(TRANSACTION_INTERFACE_ID,
TRANSACTION_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
TRANSACTION_MODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SEGMENT1,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_SOURCE_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
TRANSACTION_TYPE_NAME,
TRANSACTION_SOURCE_TYPE_NAME,
SECONDARY_INVENTORY_NAME,
ACTUAL_COST,
STATUS_FLAG)
VALUES
(I,
REC_CUR_STG.TRANSACTION_ID,
REC_CUR_STG.SOURCE_CODE,
REC_CUR_STG.SOURCE_LINE_ID,
REC_CUR_STG.HEADER_ID,
REC_CUR_STG.TRANSACTION_MODE,
REC_CUR_STG.LAST_UPDATE_DATE,
REC_CUR_STG.LAST_UPDATED_BY,
REC_CUR_STG.CREATION_DATE,
REC_CUR_STG.CREATED_BY,
REC_CUR_STG.LAST_UPDATE_LOGIN,
REC_CUR_STG.INVENTORY_ITEM_ID,
REC_CUR_STG.SEGMENT1,
REC_CUR_STG.ORGANIZATION_ID,
REC_CUR_STG.TRANSACTION_QUANTITY,
REC_CUR_STG.TRANSACTION_UOM,
REC_CUR_STG.TRANSACTION_DATE,
REC_CUR_STG.TRANSACTION_SOURCE_ID,
REC_CUR_STG.TRANSACTION_SOURCE_TYPE_ID,
REC_CUR_STG.TRANSACTION_ACTION_ID,
REC_CUR_STG.TRANSACTION_TYPE_ID,
REC_CUR_STG.TRANSACTION_TYPE_NAME,
REC_CUR_STG.TRANSACTION_SOURCE_TYPE_NAME,
REC_CUR_STG.SECONDARY_INVENTORY_NAME,
REC_CUR_STG.ACTUAL_COST,
'N');
I:=I+1;
COMMIT;
END LOOP;
COMMIT;
END P1;


==============================================================================

CREATE OR REPLACE PROCEDURE P2(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
AS
L_TRANSACTION_ID MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID%TYPE;
L_SOURCE_CODE MTL_MATERIAL_TRANSACTIONS.SOURCE_CODE%TYPE;
L_SOURCE_LINE_ID MTL_MATERIAL_TRANSACTIONS.SOURCE_LINE_ID%TYPE;
L_SOURCE_HEADER_ID OE_ORDER_HEADERS_ALL.HEADER_ID%TYPE;
L_TRANSACTION_MODE MTL_MATERIAL_TRANSACTIONS.TRANSACTION_MODE%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_SEGMENT1 MTL_SYSTEM_ITEMS_B.SEGMENT1%TYPE;
L_TRANSACTION_UOM MTL_MATERIAL_TRANSACTIONS.TRANSACTION_UOM%TYPE;
L_TRANSACTION_SOURCE_ID MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_ID%TYPE;
L_TRANSACTION_SOURCE_TYPE_ID MTL_TXN_SOURCE_TYPES.TRANSACTION_SOURCE_TYPE_ID%TYPE;
L_TRANSACTION_ACTION_ID MTL_TRANSACTION_TYPES.TRANSACTION_ACTION_ID%TYPE;
L_TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES.TRANSACTION_TYPE_ID%TYPE;
L_TRANSACTION_TYPE_NAME MTL_TRANSACTION_TYPES.TRANSACTION_TYPE_NAME%TYPE;
L_TRANSACTION_SOURCE_TYPE_NAME MTL_TXN_SOURCE_TYPES.TRANSACTION_SOURCE_TYPE_NAME%TYPE;
L_SECONDARY_INVENTORY_NAME MTL_SECONDARY_INVENTORIES.SECONDARY_INVENTORY_NAME%TYPE;
L_ORGANIZATION_ID1 MTL_SECONDARY_INVENTORIES.ORGANIZATION_ID%TYPE;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_VAL_ISS_RCP
IS
SELECT A.*,ROWID
FROM XX_ITEM_ISS_REC A
WHERE STATUS_FLAG='N';
BEGIN
FOR REC_CUR_VAL_ISS_RCP IN CUR_VAL_ISS_RCP
LOOP
L_ORGANIZATION_ID:=NULL;
L_INVENTORY_ITEM_ID:=NULL;
BEGIN
SELECT SOURCE_CODE
INTO L_SOURCE_CODE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE SOURCE_CODE=REC_CUR_VAL_ISS_RCP.SOURCE_CODE
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET SOURCE_CODE=L_SOURCE_CODE
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOURCE CODE DOES NOT EXIST';
END;
BEGIN
SELECT SOURCE_LINE_ID
INTO L_SOURCE_LINE_ID
FROM MTL_MATERIAL_TRANSACTIONS
WHERE SOURCE_LINE_ID=REC_CUR_VAL_ISS_RCP.SOURCE_LINE_ID
AND SOURCE_CODE=REC_CUR_VAL_ISS_RCP.SOURCE_CODE;
UPDATE XX_ITEM_ISS_REC
SET SOURCE_LINE_ID=L_SOURCE_LINE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOURCE LINE ID DOES NOT EXIST';
END;
BEGIN
SELECT HEADER_ID
INTO L_SOURCE_HEADER_ID
FROM OE_ORDER_HEADERS_ALL
WHERE HEADER_ID=REC_CUR_VAL_ISS_RCP.SOURCE_HEADER_ID;
UPDATE XX_ITEM_ISS_REC
SET SOURCE_HEADER_ID=L_SOURCE_HEADER_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SOURCE LINE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_MODE
INTO L_TRANSACTION_MODE
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_MODE=REC_CUR_VAL_ISS_RCP.TRANSACTION_MODE
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_MODE=L_TRANSACTION_MODE
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION MODE DOES NOT EXIST';
END;
BEGIN
SELECT ORGANIZATION_ID
INTO L_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID =REC_CUR_VAL_ISS_RCP.ORGANIZATION_ID;
UPDATE XX_ITEM_ISS_REC
SET ORGANIZATION_ID=L_ORGANIZATION_ID
WHERE ROWID= REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || ' ERROR ORGANIZATION ID';
END;
BEGIN
SELECT INVENTORY_ITEM_ID
INTO L_INVENTORY_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID=REC_CUR_VAL_ISS_RCP.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= L_ORGANIZATION_ID;
UPDATE XX_ITEM_ISS_REC
SET INVENTORY_ITEM_ID=L_INVENTORY_ITEM_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'ITEM DOES NOT EXIST';
END;
BEGIN
SELECT SEGMENT1
INTO L_SEGMENT1
FROM MTL_SYSTEM_ITEMS_B
WHERE SEGMENT1=REC_CUR_VAL_ISS_RCP.SEGMENT1
AND INVENTORY_ITEM_ID=REC_CUR_VAL_ISS_RCP.INVENTORY_ITEM_ID
AND ORGANIZATION_ID= L_ORGANIZATION_ID;
UPDATE XX_ITEM_ISS_REC
SET SEGMENT1=L_SEGMENT1
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SEGMENT1 DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_UOM
INTO L_TRANSACTION_UOM
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_UOM=REC_CUR_VAL_ISS_RCP.TRANSACTION_UOM
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_UOM=L_TRANSACTION_UOM
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION UOM DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_SOURCE_ID
INTO L_TRANSACTION_SOURCE_ID
FROM MTL_MATERIAL_TRANSACTIONS
WHERE TRANSACTION_SOURCE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_SOURCE_ID
AND TRANSACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_SOURCE_ID=L_TRANSACTION_SOURCE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION SOURCE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_SOURCE_TYPE_ID
INTO L_TRANSACTION_SOURCE_TYPE_ID
FROM MTL_TXN_SOURCE_TYPES
WHERE TRANSACTION_SOURCE_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_SOURCE_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_SOURCE_TYPE_ID=L_TRANSACTION_SOURCE_TYPE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION SOURCE TYPE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_ACTION_ID
INTO L_TRANSACTION_ACTION_ID
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_ACTION_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_ACTION_ID
AND TRANSACTION_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_ACTION_ID=L_TRANSACTION_ACTION_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION ACTION ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_TYPE_ID
INTO L_TRANSACTION_TYPE_ID
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_TYPE_ID=L_TRANSACTION_TYPE_ID
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION TYPE ID DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_TYPE_NAME
INTO L_TRANSACTION_TYPE_NAME
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_NAME=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_NAME
AND TRANSACTION_TYPE_ID=REC_CUR_VAL_ISS_RCP.TRANSACTION_TYPE_ID;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_TYPE_NAME=L_TRANSACTION_TYPE_NAME
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION TYPE NAME DOES NOT EXIST';
END;
BEGIN
SELECT TRANSACTION_SOURCE_TYPE_NAME
INTO L_TRANSACTION_SOURCE_TYPE_NAME
FROM MTL_TXN_SOURCE_TYPES
WHERE TRANSACTION_SOURCE_TYPE_NAME=REC_CUR_VAL_ISS_RCP.TRANSACTION_SOURCE_TYPE_NAME;
UPDATE XX_ITEM_ISS_REC
SET TRANSACTION_SOURCE_TYPE_NAME=L_TRANSACTION_SOURCE_TYPE_NAME
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'TRANSACTION SOURCE TYPE NAME DOES NOT EXIST';
END;
BEGIN
SELECT SECONDARY_INVENTORY_NAME
INTO L_SECONDARY_INVENTORY_NAME
FROM MTL_SECONDARY_INVENTORIES
WHERE SECONDARY_INVENTORY_NAME=REC_CUR_VAL_ISS_RCP.SECONDARY_INVENTORY_NAME
AND ORGANIZATION_ID = REC_CUR_VAL_ISS_RCP.ORGANIZATION_ID ;
UPDATE XX_ITEM_ISS_REC
SET SECONDARY_INVENTORY_NAME=L_SECONDARY_INVENTORY_NAME
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
EXCEPTION
WHEN OTHERS THEN
L_ERROR_MESSAGE:=L_ERROR_MESSAGE || 'SECONDARY INVENTORY NAME DOES NOT EXIST';
END;
IF L_ERROR_MESSAGE IS NULL THEN
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG = 'P'
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
ELSE
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG='E' ,
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_VAL_ISS_RCP.ROWID;
END IF;
END LOOP;
END P2;

=============================================================================
XX_ISS_RCP_LOAD
=============================================================================

CREATE OR REPLACE PROCEDURE P3(ERRBUF OUT VARCHAR2,
RETCODE OUT NUMBER)
IS
L_CREATED_BY NUMBER;
L_UPDATED_BY NUMBER;
L_CREATION_DATE DATE := SYSDATE;
L_LAST_UPDATE_DATE DATE := SYSDATE;
L_USER NUMBER;
L_ORGANIZATION_ID MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE;
L_INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE;
L_REC NUMBER := 0;
L_ERROR_MESSAGE VARCHAR2(4000);
CURSOR CUR_ISS_RCP_LOAD
IS
SELECT ROWID,A.*
FROM XX_ITEM_ISS_REC A
WHERE STATUS_FLAG='P';
BEGIN
SELECT USER_ID
INTO L_USER
FROM FND_USER
WHERE UPPER (USER_NAME) = 'OPERATIONS';
DELETE MTL_TRANSACTIONS_INTERFACE;
COMMIT;
FOR REC_CUR_ISS_RCP_LOAD IN CUR_ISS_RCP_LOAD
LOOP
L_ERROR_MESSAGE :=NULL;
BEGIN
INSERT INTO MTL_TRANSACTIONS_INTERFACE
( SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
TRANSACTION_QUANTITY,
TRANSACTION_UOM,
TRANSACTION_DATE,
TRANSACTION_TYPE_ID,
TRANSACTION_SOURCE_TYPE_ID,
SUBINVENTORY_CODE,
REQUIRED_FLAG
)
VALUES
( REC_CUR_ISS_RCP_LOAD.SOURCE_CODE,
OE_ORDER_LINES_S.NEXTVAL,
OE_ORDER_HEADERS_S.NEXTVAL,
'1',
'3',
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
'-1',
REC_CUR_ISS_RCP_LOAD.INVENTORY_ITEM_ID,
REC_CUR_ISS_RCP_LOAD.ORGANIZATION_ID,
REC_CUR_ISS_RCP_LOAD.TRANSACTION_QUANTITY,
REC_CUR_ISS_RCP_LOAD.TRANSACTION_UOM,
SYSDATE,
REC_CUR_ISS_RCP_LOAD.TRANSACTION_TYPE_ID,
NULL,
REC_CUR_ISS_RCP_LOAD.SECONDARY_INVENTORY_NAME,
'Y'
);
EXCEPTION
WHEN OTHERS
THEN
L_ERROR_MESSAGE := L_ERROR_MESSAGE || ' LOAD ERROR' || '-' || SQLERRM;
END;
IF L_ERROR_MESSAGE IS NOT NULL
THEN
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG = 'E',
ERROR_MESSAGE = ERROR_MESSAGE || L_ERROR_MESSAGE
WHERE ROWID=REC_CUR_ISS_RCP_LOAD.ROWID;
ELSE
UPDATE XX_ITEM_ISS_REC
SET STATUS_FLAG='P'
WHERE ROWID=REC_CUR_ISS_RCP_LOAD.ROWID;
END IF;
COMMIT;
L_REC:=L_REC+1;
END LOOP;
END P3;

========================================================================

Go to

setup - Transactions - Interface Managers

from Tools menu - click "Launch Manager"
then automatically import program runs.