Standard Purchase Order Interface Process

Step1: First we can get the flat file (Data file) from functional Consultant or Team Lead. Based on flat file you can created the Staging Tables.
Step2: Move the data from flat file to Staging tables using SQL * Loader/TOAD.
Step3:Create the package Procedure for Validation in that procedure you can do the all Validations.
Please find some of the Generic Validation.

check the PO existance
Identify Duplicate PO's
validate bill_to ship_to (inventory locations)
validate operating unit
validate buyer
Validate PO_TYPE
validate currency
validate vendor
validate vendor site code
Validate poheader dff type (if you required)
Validate Payment terms
validate fob code
validate freight terms

Line and Distribution level validations:

Validate Item Code and Description
Validate Item Category
validate receiving inv org code(po org)
get uomcode
get secondary qty
validate line type
validates charge account segments validation

step4: once validation Program runs without validtion failed after that run the replica loading program
which was moving the data from staging tables to interface replica tables .
step5: now run the loading program ...this program will take of moving the data from interface replica tables to interface tables.
interface tables are:

po_headers_interface
po_lines_interface

step5:once the interface tables are loaded the data fine then we can run the import purchase order progam
here we have pass the parameters.
then
go back end
see the all the base tables

po_headers_all
po_lines_all
po_line_locactions_all
po_distributions_all

GOOD LUCK.........

if you have any issues please give me a comment............

PO Form Customization

The purpose of this deliverable is to document the functional specifications for the Customization of PO form to ensure no PO is created for less than US$1000.If  enterd amount is less than it will pop-up a messgae.” Amount Should Be Greater Then $1000.Transaction Not Allowed'.if enterd amount Greater then $1000 it will allow to book the order .(CUSTOM.pll.).
Export program components

The following list contains all components required to completely install the PO form customization using CUSTOM.pll

1.  Open CUSTOM.pll in Oracle Forms Developer.
2. Modify the text of the CUSTOM package body
3. Compile all and save your changes. Exit from Oracle Forms developer.

4. Use the Oracle Forms Compiler program to generate a new .plx file for the CUSTOM library.

5. Verify that your file generated successfully. Place the .plx file in AU_TOP/resource.

6. Try it out from the Oracle Applications Navigator.

PO form customization using CUSTOM.pll  library

The CUSTOM.pll program performs the following steps:
1.      Declare The Variables.
2.      Write The Condition.
3.      Save & Compile.

      Filename                                  CUSTOM.pll
Directory                                 $AU_TOP/resources
Package name                         CUSTOM
Functions                                RETURN BOOLEAN
Procedure                                EVENT                                
      Form  Name                            POXPOEPO

1. Declare The Variables In Custom.pll (po)
form_name      varchar2(30) := name_in('system.current_form');
block_name     varchar2(30) := name_in('system.cursor_block');
 field_name     varchar2(30) := name_in('system.current_field');
 po_line_amount number(20);
 po_unit_amount number(20);
       po_total_amount number(20):=1000;

2.Write The Fallowing Code


form_name      varchar2(30) := name_in('system.current_form');
 block_name     varchar2(30) := name_in('system.cursor_block');
 field_name     varchar2(30) := name_in('system.current_field');
 
  po_line_amount number(20);
po_unit_amount number(20);
po_total_amount number(20):=1000;
po_limit_dsp number(20):=1000;
--po_limit_dsp1 number(20):=1000;
--vl1 varchar2(50):=name_in('PO_HEADERS.DOC_TYPE_NAME');
--AMT_LIMIT_DSP
--DOC_TYPE_NAME
  begin
    if (event_name='WHEN-NEW-FORM-INSTANCE') then
set_window_property(FORMS_MDI_WINDOW, TITLE,'Rajsekhar apps');
end if;
    null;
     --------PO FORM CUSTOMIZATION-----
 -- begin
if(event_name='WHEN-VALIDATE-RECORD') then
if(form_name='POXPOEPO' and block_name='PO_LINES') then
if name_in('PO_HEADERS.DOC_TYPE_NAME')='Standard Purchase Order' or name_in('PO_HEADERS.DOC_TYPE_NAME')='Planned Purchase Order' then
po_line_amount :=name_in('PO_LINES.QUANTITY');
po_unit_amount :=name_in('PO_LINES.UNIT_PRICE');
po_total_amount:=po_line_amount*po_unit_amount;
if po_total_amount<1000 then
fnd_message.set_string('Amount Should Be Greater Then $1000.Transaction Not Allowed');
fnd_message.show;
RAISE Form_Trigger_Failure;
--close_form('POXPOEPO');
--          else
--          message('accepted');
--          message('accepted');
end if;
end if;
end if;
end if;
if(event_name='WHEN-VALIDATE-RECORD') then
if(form_name='POXPOEPO' and block_name='PO_HEADERS') then
if name_in('PO_HEADERS.DOC_TYPE_NAME')='Contract Purchase Agreement' or name_in('PO_HEADERS.DOC_TYPE_NAME')='Blanket Purchase Agreement' then
po_limit_dsp :=name_in('PO_HEADERS.AMT_LIMIT_DSP');
if po_limit_dsp<1000 then
fnd_message.set_string('Amount Should Be Greater Then $1000.Transaction Not Allowed');
fnd_message.show;
message('REQUIRED');
message('REQUIRED');
RAISE Form_Trigger_Failure;
--close_form('POXPOEPO');
--          else
--          message('accepted');
--message('accepted');
--commit_form;
end if;
end if;
end if;
end if;
 end event;

Pending Purchase Orders Report Code

select sysdate REP_DATE,

pv.SEGMENT1 VENDOR_NUM,

ph.segment1 PO_NUM,

PH.REVISION_NUM,

ph.type_lookup_code PO_TYPE,

pol.ITEM_DESCRIPTION,

pol.UNIT_MEAS_LOOKUP_CODE,

pol.UNIT_PRICE,hr.name,

pds.QUANTITY_ORDERED,

PDS.QUANTITY_CANCELLED,

pds.QUANTITY_DELIVERED,

pds.QUANTITY_BILLED,

pds.AMOUNT_BILLED,

pol.CANCEL_DATE,

pds.QUANTITY_ORDERED-pds.QUANTITY_DELIVERED REMANING_QTY,

pv.vendor_name Supplier,

PH.AUTHORIZATION_STATUS STATUS,

PH.CREATION_DATE po_date

from po_headers_all ph,

po_lines_all pol,

po_distributions_all pds,

HR_OPERATING_UNITS hr,

po_vendors pv

where ph.PO_HEADER_ID=pol.PO_HEADER_ID

and ph.PO_HEADER_ID=pds.PO_HEADER_ID

and ph.VENDOR_ID=pv.VENDOR_ID

and hr.ORGANIZATION_ID=ph.ORG_ID

AND PdS.QUANTITY_ORDERED<>PdS.QUANTITY_DELIVERED

AND ph.TYPE_LOOKUP_CODE<>'RFQ'

AND ph.TYPE_LOOKUP_CODE<>'QUOTATION'

AND PH.AUTHORIZATION_STATUS='APPROVED'

and hr.NAME=:org

and trunc(ph.creation_date)=trunc(:dt)

and to_char(ph.creation_date,initcap('mon')||'-'||'yy')=:mon

and trunc(ph.creation_date)  between trunc(:inp) - 7   and trunc(:inp)

PO Matching Report

SELECT

      PHA.TYPE_LOOKUP_CODE,

      PHA.LAST_UPDATE_DATE,

      PHA.SEGMENT1,

      PHA.SUMMARY_FLAG,

      PHA.ENABLED_FLAG,

      PHA.APPROVED_FLAG,

      PLA.UNIT_MEAS_LOOKUP_CODE,      

      PLA.QUANTITY,

      PLA.UNIT_PRICE,

      PLA.LIST_PRICE_PER_UNIT,

      PLA.PRICE_TYPE_LOOKUP_CODE,

      PLL.RECEIPT_REQUIRED_FLAG,

      PLL.INSPECTION_REQUIRED_FLAG,decode

         (INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')   

                              Matching,       

      PLL.ENCUMBERED_FLAG,

      PDA.QUANTITY_ORDERED,      

      PDA.ACCRUAL_ACCOUNT_ID,

      PDA.VARIANCE_ACCOUNT_ID

 FROM

       PO_HEADERS_ALL PHA,

       PO_LINES_ALL PLA,

       PO_LINE_LOCATIONS_ALL PLL,

       PO_DISTRIBUTIONS_ALL PDA

 WHERE

        PDA.PO_HEADER_ID=PLL.PO_HEADER_ID

AND          

       PLL.PO_HEADER_ID=PLA.PO_HEADER_ID

AND

       PLA.PO_HEADER_ID=PDA.PO_HEADER_ID

AND        

     

       PLA.UNIT_PRICE>1000000000

AND

      decode

(INSPECTION_REQUIRED_FLAG||RECEIPT_REQUIRED_FLAG,'NN','2-Way','NY','3-Way','YY','4-Way','Not Specified')=:MATCH LEVEL