Po Level Conversion In Interface With Detail Process
Through
Open Interface Tables, Oracle Open Purchase Order to be created.
Instead of Creating Purchase
Order from Oracle front end screen, Open Interface program developed to create
purchase order.
User enters the requested
details to create po into Custom Table. When execute the Program Xx_Create_Po_Opi
, it
creates open purchase order with mentioned line items.
Custom
Table Xx_Po_Hdr_Table is Created to
get the below input from users.
·
HNO
·
Supplier Name
·
Supplier Site
·
Ship To Location
·
Bill to Location
·
Org Id
·
Currency Code
·
Charge Account Id
This
table has unique HNO(Header No) column.
And the table
Xx_Po_Lines_Table is created to get
the line item details. This table consists more than one line details for a
single Po Header.
·
HNO
·
LNO
·
Item
·
Quanity
·
Unit Selling Price
·
Need By Date
The Lines in Xx_Po_Lines_Table has the unique
LNO(LineNumber Column) And the HNO column with respect to the table Xx_Po_Hdr_Table HNO Column.
The user can
enter the line item details only when the HNO exist in the Header(XX_PO_HDR_TABLE).
When executes
the procedure Xx_Po_Create_Opi, it picks up the records
from the custom Header (XX_PO_HDR_TABLE) table with Created_Flag=’N’ .
This
Program inserts the header details into Po_Headers_Interface and its
corresponding item details into Po_Lines_Interface table.The Item details will
be inserted after validating the item.
And
It will insert the charge_account_id and quantity into table Po.Po_Distributions_Interface
with respect to the item.
Once the
above values are inserted into interface tables, created_flag to be updated as
‘Y’ and po_int_hdr_id to be updated by above created interface_header_id.
After that the
interface program to be submitted 'POXPOPDOI' by procedure for the mentioned
organization.The Purchase Order Header, Line
and Distributions will be created once the interface program completed
successfully.
Basic Purpose
This Procedure
Creates Open Purchase Order.
2.1
SQL & PL/SQL Statements(Optional)
Table:
create table
xx_PO_Hdr_TABLE
(
HNO Number,
Batch_Id Number,
Currency_Code Varchar2(5),
Agent_Id
Number,
Vendor_Id Number,
Vendor_Site_Id Number,
Ship_To Number,
Bill_To Number,
Org_Id Number,
Charge_acct_id
Number,
CREATED_FLAG
Varchar2(1),
Po_int_hdr_id
Number,
creation_date
date,
last_updated_date
date)
create table xx_po_lines_table
(
HNO Number,
LNO Number,
Item Varchar2(50),
Qty Number,
Unit_Price Number,
need_by_date date,
CREATED_FLAG Varchar2(1),
po_int_hdr_id number,
po_int_line_id number,
creation_date date,
last_updated_date date)
ALTER TABLE xx_PO_Hdr_TABLE
add CONSTRAINT xx_PO_Hdr_TABLE_pk PRIMARY KEY (HNO);
ALTER TABLE xx_po_lines_table
add CONSTRAINT xx_po_lines_table_fk FOREIGN KEY (HNO) references xx_PO_Hdr_TABLE(HNO)
ALTER TABLE xx_po_lines_table
add CONSTRAINT xx_po_lines_table_pk primary KEY (HNO,LNO)
Procedure:
Create Or Replace Procedure
Xx_Create_Po_Opi
As
V_Errcode Varchar2(100);
V_Errmsg Varchar2(500);
V_Request_Id Number;
V_Phase Varchar2(100);
V_Status Varchar2(100);
V_Dev_Phase Varchar2(100);
V_Dev_Status Varchar2(100);
V_Message Varchar2(2000);
V_Wait_Outcome Boolean;
V_Count Number:=0;
V_Acct_Id Number;
V_Item Varchar2(50);
V_Po_Hdr_Id Number;
V_Po_Line_Id Number;
Cursor C1 Is
Select * From Xx_Po_Hdr_Table
Where Nvl(Created_Flag,'N')='N' And Po_Int_Hdr_Id Is Null;
Begin
Select Po_Headers_Interface_S.Nextval Into V_Po_Hdr_Id From Dual;
For I In C1 Loop
Insert Into Po_Headers_Interface
(
Interface_Header_Id,
Batch_Id,
Action,
Document_Type_Code,
Currency_Code,
Agent_Id,
Vendor_Id,
Vendor_Site_Id,
Ship_To_Location_Id,
Bill_To_Location_Id,
Org_Id
)
Values
(
V_Po_Hdr_Id,
I.Batch_Id,
'ORIGINAL',
'STANDARD',
Upper(I.Currency_Code),
I.Agent_Id,
I.Vendor_Id,
I.Vendor_Site_Id,
I.Ship_To,
I.Bill_To,
I.Org_Id
);
Commit;
Dbms_Output.Put_Line('Inserted In Po Headers Interface Table');
Update Xx_Po_Hdr_Table Set Created_Flag='Y',Po_Int_Hdr_Id=V_Po_Hdr_Id
Where Hno=I.Hno
And Nvl(Created_Flag,'N')='N'
And Po_Int_Hdr_Id Is Null;
Commit;
Declare
Cursor C2 Is
Select * From Xx_Po_Lines_Table
Where Nvl(Created_Flag,'N')='N' And Po_Int_Hdr_Id Is Null And
Po_Int_Line_Id Is Null
And Hno=I.Hno;
Begin
For J In C2 Loop
Select Distinct(Segment1) Into
V_Item From Inv.Mtl_System_Items_B Where Segment1=Upper(J.Item);
If V_Item Is Not Null Then
V_Count:=V_Count+1;
Select
Po.Po_Lines_Interface_S.Nextval Into V_Po_Line_Id From Dual;
Insert Into Po_Lines_Interface
(Interface_Line_Id,
Interface_Header_Id,
Action,
Line_Num,
Item,
Unit_Price,
Quantity,
Need_By_Date)
Values
(
V_Po_Line_Id,
V_Po_Hdr_Id,
'Original',
V_Count,--L1.Line_Num,
Upper(V_Item),
J.Unit_Price,
J.Qty,
J.Need_By_Date
);--L1.Need_By_Date);
Commit;
Dbms_Output.Put_Line('Inserted In Po Lines Interface Table');
Insert Into
Po.Po_Distributions_Interface
(Interface_Header_Id,
Interface_Line_Id,
Interface_Distribution_Id,
Distribution_Num,
Quantity_Ordered,
Charge_Account_Id)
Values
(
V_Po_Hdr_Id,
V_Po_Line_Id,
Po.Po_Distributions_Interface_S.Nextval,
V_Count,
J.Qty,
I.Charge_Acct_Id -- Code
Combination Id For The Charge Account To Be Used On The Distribution
);
Commit;
Update Xx_Po_Lines_Table
Set Created_Flag='Y',Po_Int_Hdr_Id=V_Po_Hdr_Id,Po_Int_Line_Id=V_Po_Line_Id
Where Hno=J.Hno And
Lno=J.Lno And Nvl(Created_Flag,'N')='N' And Po_Int_Hdr_Id Is Null And
Po_Int_Line_Id Is Null;
Commit;
Dbms_Output.Put_Line('Inserted In Po Distributions Interface Table');
Else
Dbms_Output.Put_Line('Item
Not Exist In Master Table');
End If;
End Loop;
End;
Begin
Fnd_Global.Apps_Initialize(User_Id=>1184,Resp_Id=>50372,Resp_Appl_Id=>7000);
Mo_Global.Set_Policy_Context ('S', I.Org_Id);
Mo_Global.Init('PO');
V_Request_Id :=
Fnd_Request.Submit_Request ('PO'
,'POXPOPDOI'
,Null
,Null
,False
,Null
,'STANDARD'
,Null
,'N'
,Null
,'INCOMPLETE'
,Null
,Null
,I.Org_Id
,Null
);
Commit;
Dbms_Output.Put_Line('Interface Program Submitted');
Loop
V_Wait_Outcome :=
Fnd_Concurrent.Get_Request_Status(Request_Id => V_Request_Id,
Phase => V_Phase,
Status => V_Status,
Dev_Phase => V_Dev_Phase,
Dev_Status => V_Dev_Status,
Message => V_Message);
Exit When Upper(V_Phase) =
'COMPLETED';
End Loop; --Concurrent Phase
Loop End
Exception
When Others Then
V_Errcode := Sqlcode;
V_Errmsg := Sqlerrm;
Insert Into Xx_Error_Table
Values ('PO','XX_CREATE_PO_OPI',V_Errcode,V_Errmsg,Sysdate,Fnd_Global.User_Id);
Dbms_Output.Put_Line('Errcode :'||Sqlcode||'Error Message :'||Sqlerrm);
Commit;
End;
End Loop;
End;
2.2 Prerequisites & Dependencies
Custom Table Xx_Item_Table is created to get the user Input And Xx_Error_Table is used in exception part of the above procedure to
store the error details.
insert into xx_PO_Hdr_TABLE values (5,101,'INR',119,24011,34228,177,177,84,2114,'N',null,sysdate,sysdate)
insert into xx_po_lines_table values(5,1,'RM0001001',10,8,To_Date (sysdate, 'DD-MON-RRRR'),null,null,null,sysdate,sysdate)
insert into xx_po_lines_table values(5,3,'RM
2W0656_S3',3,10,To_Date (sysdate, 'DD-MON-RRRR'),null,null,null,sysdate,sysdate)
exec Xx_Create_Po_Opi
select po_int_hdr_id from xx_po_hdr_table where hno=5
select * from po.po_headers_interface where
interface_header_id=180005
select segment1 from po.po_headers_all where
po_header_id=364018
No comments:
Post a Comment