Wednesday, October 10, 2012

PO Tables, Interfaces,Reports Details



ORACLE PURCHASING TABLES

segment1 - is the system–assigned number you use to identify in forms and reports.


Table Name                                                         Columns                                                                                              

PO_REQUISITION_HEADERS_ALL            REQUISITION_HEADER_ID, PREPARER_ID,                                                                                                      SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG
                stores information about requisition headers. You need one row for each requisition header you
create. Each row contains the requisition number, preparer, status, and description.SEGMENT1 is the number you use to identify the requisition in forms and reports(unique).

PO_REQUISITION_LINES_ALL                   REQUISITION_LINE_ID,REQUISITION_HEADER_ID,
                                                                                LINE_NUM,LINE_TYPE_ID,CATEGORY_ID,
                                                                                ITEM_DESCRIPTION,UNIT_MEAS_LOOKUP_CODE ,
UNIT_PRICE, QUANTITY, DELIVER_TO_LOCATION_ID,
                                                                                TO_PERSON_ID, SOURCE_TYPE_CODE       
                stores information about requisition lines.line number, item number, item category, item description,
need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line. 
                LINE_LOCATION_ID - purchase order shipment line on which you placed the requisition. it is null if you
have not placed the requisition line on a purchase order.
                BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement
or catalog quotation line information for the requisition line.
                PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you
exploded or multisourced this requisition line.


PO_HEADERS_ALL                                          PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE,
                                                                                SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG          
                information for your purchasing documents.There are six types of documents that use PO_HEADERS_ALL
RFQs, Quotations, Standard purchase orders, Planned purchase orders, Blanket purchase orders, Contracts
can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using
PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders.
if we use copy document Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID.

PO_LINES_ALL                                                 PO_LINE_ID, PO_HEADER_ID, LINE_TYPE_ID, LINE_NUM
                stores current information about each purchase order line.  CONTRACT_NUM reference a contract
purchase order from a standard purchase order line.

PO_VENDORS                                                    VENDOR_ID, VENDOR_NAME, SEGMENT1,
                                                                                SUMMARY_FLAG, ENABLED_FLAG          
                information about your suppliers.purchasing, receiving, payment, accounting, tax, classification, and general information.

PO_VENDOR_SITES_ALL                              VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE
                information about your supplier sites.a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this
information to store supplier address information.

PO_DISTRIBUTIONS_ALL                             PO_DISTRIBUTION_ID, PO_HEADER_ID, PO_LINE_ID,LINE_LOCATION_ID, SET_OF_BOOKS_ID,
                                                                                CODE_COMBINATION_ID,QUANTITY_ORDERED,
                                                                                DISTRIBUTION_NUM      
                contains accounting distribution information fora purchase order shipment line.You need one row for
each distribution line you attach to a purchase order shipment.
                There are four types of documents using distributions in Oracle Purchasing:
Standard Purchase Orders, Planned Purchase Orders, Planned Purchase Order Releases, Blanket Purchase Order Releases
                includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution.

PO_RELEASES_ALL                                        PO_RELEASE_ID, PO_HEADER_ID, RELEASE_NUM,
                                                                                AGENT_ID, RELEASE_DATE           
                contains information about blanket and planned purchase order releases. You need one row for each release you issue
                for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment

PO_VENDOR_CONTACTS                              VENDOR_CONTACT_ID, VENDOR_SITE_ID        
stores information about contacts for a supplier site. You need one row for each supplier contact you define.
Each row includes the contact name and site.



PO_ACTION_HISTORY                                   OBJECT_ID, OBJECT_TYPE_CODE , OBJECT_SUB_TYPE_CODE,                 SEQUENCE_NUM          
                information about the approval and control history of your purchasing documents. There is one record in
this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition.
                stores object_id -- Document header identifier,OBJECT_TYPE_CODE --- Document  type, OBJECT_SUB_TYPE_CODE --Document subtype SEQUENCE_NUM --Sequence of the approval or control action for a document

PO_REQ_DISTRIBUTIONS_ALL                  DISTRIBUTION_ID, REQUISITION_LINE_ID, SET_OF_BOOKS_ID,
                                                                                CODE_COMBINATION_ID,REQ_LINE_QUANTITY,
                                                                                DISTRIBUTION_NUM   
                stores information about the accounting distributions associated with each requisition line.


PO_LINE_LOCATIONS_ALL                         LINE_LOCATION_ID, LAST_UPDATE_DATE,                                                                                    LAST_UPDATED_BY, PO_HEADER_ID, PO_LINE_ID,                                                                                     SHIPMENT_TYPE
                contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each  schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:
                RFQs,Quotations,Standard purchase orders,Planned purchase orders,Planned purchase order releases,Blanket purchase orders, Blanket purchase order releases
                Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses
this information to record delivery schedule information for purchase orders, and price break information for
blanket purchase orders, quotations and RFQs.




                         PO Interfaces

PO_HEADERS_INTERFACE
is the interface table that imports header information from e– Commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_HEADER_ID
BATCH_ID
INTERFACE_SOURCE_CODE

PO_LINES_INTERFACE
is the interface table that imports lines information from e– commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_LINE_ID
INTERFACE_HEADER_ID
ACTION NULL Action to be completed: New or Add
GROUP_CODE (Null) Indicates the grouping of the requisition lines

PO_DISTRIBUTIONS_INTERFACE
is the interface table that imports distribution information from e– Commerce Gateway for blanket purchase orders and catalog quotations.
INTERFACE_HEADER_ID
INTERFACE_LINE_ID
INTERFACE_DISTRIBUTION_ID

ORG_ID


PO_REQUISITIONS_INTERFACE_ALL
contains requisition information from other applications.  Import feature uses this information to create new requisition headers, lines and distributions.

TRANSACTION_ID (PK) Transaction unique identifier

PROCESS_FLAG NULL   Transaction processing state

PO_REQ_DIST_INTERFACE_ALL
Is the interface table that creates multiple distributions using Requisition Import.
ACCRUAL_ACCOUNT_ID Unique identifier for the General Ledger accrual account
ALLOCATION_TYPE Specifies the method of allocation across distributions. Can be PERCENT.
ALLOCATION_VALUE     Allocation split value

BATCH_ID              Import batch identifier

BUDGET_ACCOUNT_ID    Unique identifier for the General Ledger budget account
CHARGE_ACCOUNT_ID    Unique identifier for the General Ledger charge account




RCV_HEADERS_INTERFACE
is the interface table that stores receiving header information.

HEADER_INTERFACE_ID Interface EDI header unique identifier

GROUP_ID NULL        Interface group for set processing
EDI_CONTROL_NUM      EDI transaction control number if data
is sent via EDI
PROCESSING_STATUS_CODE  Processing status of the interface
header row
RECEIPT_SOURCE_CODE Source type of the shipment

ASN_TYPE NULL        The document type: values are

ASN, ASBN, or RECEIVE
TRANSACTION_TYPE    The transaction purpose code:    values are
NEW, REPLACE, ADD,or CANCEL

RCV_LOTS_INTERFACE
Holds temporary lot number transaction records for a parent record in the RCV_TRANSACTIONS_INTERFACE table.

RCV_TRANSACTIONS_INTERFACE
stores information about receiving transactions that are waiting to be processed by the receiving transaction processor or were rejected due to an error when the transaction processor attempted to process the transaction.
TRANSACTION_TYPE
TRANSACTION_DATE
PROCESSING_STATUS_CODE

Interface Table  --PO_REQUISITION_INTERFACE_ALL
Program Name to Launch the PO Import program -Requisition Import program
In the first phase, the program validates your data and derives or defaults additional information. The program generates an error message for every validation that fails and creates a row
PO_REQUISITIONS_INTERFACE_ALL
It contains requisition information from other applications.  Each row includes all the information necessary to create  approved or unapproved requisitions    in Oracle Purchasing.
The Oracle Purchasing Requisition Import feature uses this information to create new requisition headers, lines and distributions.

PO_REQUISITIONS_INTERFACE_ALL
table are identical to the corresponding columns in the
PO_REQUISITIONS_HEADERS_ALL,
PO_REQUISITION_LINES_ALL and PO_REQ_DISTRIBUTIONS_ALL
tables.

Po_requisition_interface_all
INTERFACE_SOURCE_CODE      --VDP
,SOURCE_TYPE_CODE               --INVENTORY
,DESTINATION_TYPE_CODE                   --EXPENSE
,AUTHORIZATION_STATUS                    --Status Always Approved we will get ..
,REQ_NUMBER_SEGMENT1            --Request id
,PREPARER_ID                    -- ASSOCIATE ID comes from VDP
,LINE_TYPE_ID
,LINE_TYPE                      --vArchar Requisition line type name
,QUANTITY                       --Quantity
,UNIT_PRICE
,CHARGE_ACCOUNT_ID             -- to be hard coded based on the line type
,DESTINATION_ORGANIZATION_ID   --v-org_id
,DELIVER_TO_LOCATION_ID        -- location id
,DELIVER_TO_REQUESTOR_ID       -- ASSOCIATE ID  Same as Prepare_id
,ITEM_ID  -- item code
,ITEM_DESCRIPTION           --Based on the item code description is taken
,CATEGORY_ID
,UOM_CODE
,CURRENCY_CODE           --if this is supplied then next two should be filled
,RATE
,RATE_DATE
,RATE_TYPE
If you use Oracle Master Scheduling/MRP or a non– Oracle MRP system with Oracle Purchasing, you may find that you need to reschedule requisitions as your planning requirements change.

Reschedule Interface Table Since you have already loaded your requisitions into Oracle Purchasing, you simply need to identify for Oracle Purchasing the requisition lines you want to reschedule. After you identify each line to reschedule, you can update the quantity and the need– by date for the corresponding requisition line.
PO_RESCHEDULE_INTERFACE table for  each change you want to make to a requisition. Each row includes the requisition line identifier, the new quantity, and the new need– by date for the requisition line. You run the Requisition Reschedule program to implement the changes. Import Requisitions from the External System to Oracle Purchasing Write a  Import Program to load the external data  Dump data into the some interface table  .Validate the data  through some PL/SQL
(Applying Business Logic---
Run Import Program
Requisition is imported and created as many
                        number of requisition as successful records .
Others -if error then run exceptional report to see the reason of failure.



                               PO Reports

Open Purchase Orders Report (by Buyer)
The Open Purchase Orders Report (by Buyer) lists all or specific open purchase orders that relate to buyers.

Overshipments Report
The Overshipments Report lists purchase order receipts with a quantity received greater than the quantity ordered.

Location Listing
The Location Listing shows internal organizations locations and addresses. Purchasing lets you specify which locations you want to review: Ship To, Bill To, Office, Internal, and Receiving.

Buyer Listing
The Buyer Listing shows the buyer name, default purchasing category, ship– to location, and effective dates of all buyers or a selected set of buyers.

Blanket and Planned PO Status Report
The Blanket and Planned PO Status report can be used to review purchase order transactions for items you buy, using blanket purchase agreements and planned purchase orders.

Cancelled Requisitions Report
Use the Cancelled Requisitions Report to review information on cancelled requisitions.

Invoice Price Variance Report
The Invoice Price Variance Report shows the variance between the invoice price and the purchase price for all inventory and work in process related invoice distributions. Payables records invoice price variances when the invoices are matched, approved, and posted.


                              Po Conversion Package

CREATE OR REPLACE PACKAGE BODY GEPS_PURCHASE_CONV AS
--G_BATCH_ID NUMBER;
--G_POXPOPDOI_REQUEST_ID number;
   g_batch_id1         number;
   g_batch_id2         number;
   g_batch_id3         number;
   g_batch_id4         number;

FUNCTION GET_APPLICATION_ID (P_APP_SHORT_NAME  IN  VARCHAR2)
RETURN NUMBER
IS
V_APP_ID  NUMBER;
V_ERR_TEXT VARCHAR2(240);
 BEGIN
       SELECT APPLICATION_ID INTO  V_APP_ID
     FROM FND_APPLICATION
    WHERE APPLICATION_SHORT_NAME = P_APP_SHORT_NAME;
        RETURN(V_APP_ID);
 END;
------------------------------------------------------------------------------------------------------
FUNCTION GET_RESP_ID (p_app_id IN NUMBER)
RETURN NUMBER
IS
v_responsibility_id NUMBER;
  BEGIN
    SELECT responsibility_id INTO v_responsibility_id
 FROM FND_RESPONSIBILITY_VL
 WHERE responsibility_name = 'Purchasing Super User'
 AND application_id=p_app_id;
 RETURN(v_responsibility_id);
  END;
-------------------------------------------------------------------------------------------------------
PROCEDURE GEPS_PO_PRC (errbuf  OUT  VARCHAR2
              ,retcode OUT  VARCHAR2
          ,p_from_po varchar2
          ,p_to_po varchar2
          ,p_batch_id number
          ,p_agent_id number)
AS
 TYPE rec_gpd IS RECORD
    ( po                 GEPS_PO_DETAILS.po%type
     ,po_line            GEPS_PO_DETAILS.po_line%type
     ,po_shipment        GEPS_PO_DETAILS.po_shipment%type
    );
 TYPE p_gpd_tbl_type IS TABLE OF rec_gpd INDEX BY BINARY_INTEGER;
 v_gpd_array p_gpd_tbl_type;

cursor po_header_cur(p_n01_op_unit number) is
SELECT  poh.segment1 po,
  poh.po_header_id, poh.ship_via_lookup_code,
  poh.vendor_id, poh.vendor_site_id,
  pvsa.vendor_site_code,poh.agent_id
FROM  PO_HEADERS_ALL POH,
      PO_VENDOR_SITES_ALL pvsa
WHERE  poh.segment1  in (select  distinct po from GEPS_PO_DETAILS where PO_TRANSFER_FLAG IS NULL and po between p_from_po and p_to_po)--and po='424360711')
AND  poh.org_id  = p_n01_op_unit
And  poh.type_lookup_code = 'STANDARD'
AND  poh.vendor_site_id = pvsa.vendor_site_id
And    poh.org_id = pvsa.org_id;


CURSOR po_det_cur (p_h03_org_id IN NUMBER,p_po_header_id number, p_po_line number, p_po_shipment varchar2,p_n01_op_unit number)
IS
SELECT   pol.line_num po_line, pll.shipment_num po_shipment,
   pol.po_header_id,
  pol.po_line_id, pol.item_id,
  pll.line_location_id
FROM
  PO_LINES_ALL POL,
  PO_LINE_LOCATIONS_ALL PLL,
  MTL_SYSTEM_ITEMS_B MSI
WHERE  pol.po_header_id=p_po_header_id
AND  pol.line_num  = p_po_line
AND  pol.po_line_id  = pll.po_line_id
and  pll.shipment_num = p_po_shipment
AND  pol.org_id  = p_n01_op_unit
AND  msi.inventory_item_id = pol.item_id
AND  msi.organization_id = p_h03_org_id;

/*AND  msi.planner_code IN ('CAP1', 'CAP2', 'CAP3',
'HUNSA2',
        'HUNSA1', 'SVCSHOP', 'HUNSA4',
                             'FSTM', 'FGAS', 'FGEN', 'FSAL', 'HAZ',
                             'MISC', 'NOR', 'GRE', 'SUPERSEDE1');
*/




v_op_unit_N01  ORG_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
v_op_unit_H03  ORG_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
v_org_id_N01  ORG_ORGANIZATION_DEFINITIONS.organization_id%TYPE;
v_org_id_H03  ORG_ORGANIZATION_DEFINITIONS.organization_id%TYPE;
v_vend_site_id_h03  po_vendor_sites_all.vendor_site_id%TYPE;
v_vend_id_h03  po_vendor_sites_all.vendor_id%TYPE;
v_vendor_contact_id po_vendor_contacts.vendor_contact_id%TYPE;
v_freight_H03  org_freight_tl.freight_code%TYPE;
v_ship_to_location_id hr_locations_all.location_id%TYPE;
v_date   NUMBER ;--:= TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS');
---vars for compilation status-------
  l_request_number   NUMBER;
  l_phase            VARCHAR2(20);
  l_status      VARCHAR2(20);
  l_dev_phase      VARCHAR2(20);
  l_dev_status      VARCHAR2(20);
  l_message      VARCHAR2(300);
  l_retun_flag       VARCHAR2(1);
  l_bool_return      BOOLEAN;
  v_request_id  NUMBER;

  v_batch number;
  v_po_header_interface_id number;

  v_agent_id     po_agents.agent_id%type;
BEGIN

--G_BATCH_ID:=v_date;
v_date:=p_batch_id;

 BEGIN
  SELECT operating_unit,organization_id
  INTO   v_op_unit_N01,v_org_id_N01
  FROM   ORG_ORGANIZATION_DEFINITIONS
  WHERE  organization_code = 'N01';
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
      FND_FILE.PUT_LINE(FND_FILE.LOG,'2'||SQLERRM );
  END;
 BEGIN
  SELECT operating_unit,organization_id
  INTO   v_op_unit_H03,v_org_id_H03
  FROM   ORG_ORGANIZATION_DEFINITIONS
  WHERE  organization_code = 'H03';
 EXCEPTION
           WHEN NO_DATA_FOUND THEN
         FND_FILE.PUT_LINE(FND_FILE.LOG,'3'||SQLERRM );
 END;




 BEGIN
         SELECT location_id
         INTO v_ship_to_location_id
         FROM hr_locations_all
         WHERE location_code = 'GAUSH03';
        EXCEPTION
         WHEN NO_DATA_FOUND
          THEN v_ship_to_location_id := NULL;
        END;

v_batch:=0;

for po_header_rec in po_header_cur(v_op_unit_N01)
loop

begin
select     PO_HEADERS_INTERFACE_S.NEXTVAL
into v_po_header_interface_id
from dual ;
BEGIN
    SELECT  pvsa.vendor_site_id, pvsa.vendor_id
    INTO  v_vend_site_id_h03, v_vend_id_h03
    FROM  po_vendor_sites_all pvsa
    WHERE  pvsa.vendor_site_code  = po_header_rec.vendor_site_code
    AND  pvsa.vendor_id  = po_header_rec.vendor_id
    AND  pvsa.org_id  = v_op_unit_H03;
    BEGIN
     SELECT pvc.vendor_contact_id
     INTO v_vendor_contact_id
     FROM po_vendor_contacts pvc
     WHERE pvc.vendor_site_id = v_vend_site_id_h03
     AND     ROWNUM=1;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
      v_vendor_contact_id := NULL;
    END;
    BEGIN
     SELECT  freight_code
     INTO    v_freight_H03
     FROM  org_freight_tl
     WHERE  UPPER(freight_code) = UPPER(po_header_rec.ship_via_lookup_code)
     AND  organization_id  = v_org_id_H03
     AND LANGUAGE  = 'US';
    EXCEPTION WHEN OTHERS THEN
     v_freight_H03 := NULL;
--     fnd_file.put_line(fnd_file.LOG,'9'||SQLERRM );
    END;

    begin
    v_agent_id:=null;
    SELECT pa.agent_id
    into v_agent_id
    FROM po_agents pa
         ,PER_ALL_PEOPLE_F paf
    WHERE pa.agent_id=po_header_rec.agent_id
    and paf.PERSON_ID=pa.agent_id
    AND SYSDATE BETWEEN pa.start_date_active AND nvl(pa.end_date_active,sysdate+1)
    and  sysdate between paf.effective_start_date and nvl(paf.effective_end_date,sysdate+1);
    if v_agent_id=16016 then
    v_agent_id:=p_agent_id;
    end if;

    exception
    when no_data_found then
    v_agent_id:=p_agent_id;
    end;
--  fnd_file.put_line(1,'headers int loop');
/********* Insert into Headers Interface Table ************/
    INSERT INTO po_headers_interface(interface_header_id,
        action,
        org_id,
        BATCH_ID,
        document_type_code,
        vendor_id,
        vendor_site_code,
        vendor_site_id,
        vendor_contact_id,
        --effective_date,
        --expiration_date,
        load_sourcing_rules_flag,
        agent_id,
        document_subtype,
        currency_code,
        ship_to_location,
        --bill_to_location_id,
        document_num,
        comments,
        reply_date,
        approval_required_flag,
        terms_id,
        process_code,
        vendor_doc_num,
        from_rfq_num,
        freight_terms,
        fob,
        freight_carrier,
        reply_method,
        note_to_vendor,
        quote_warning_delay,
        approval_status,
        attribute_category,
        attribute1,
         attribute2,
        attribute3,
        attribute15--,
--        LAST_UPDATE_DATE,
--        LAST_UPDATED_BY,
--        CREATION_DATE,
--        CREATED_BY,
--        LAST_UPDATE_LOGIN
         )
SELECT v_po_header_interface_id,--PO_HEADERS_INTERFACE_S.NEXTVAL,--interface_header_id,
    'ADD',--action,
    v_op_unit_H03,--org_id,
    v_date,--BATCH_ID,
    type_lookup_code,--document_type_code,
    vendor_id,
    po_header_rec.vendor_site_code,--vendor_site_code,
    v_vend_site_id_H03,--vendor_site_id,
    v_vendor_contact_id, --vendor_contact_id,
    --SYSDATE, --effective_date,
    --SYSDATE+365,--expiration_date,
    'N',--load_sourcing_rules_flag,
    v_agent_id,--agent_id,
    NULL,--document_subtype,
    currency_code,
    'GAUSH03',--ship_to_location,
    --bill_to_location_id,
    NULL,--document_num,
    comments,
    reply_date,
    approval_required_flag,
    terms_id,
    'PENDING',--process_code,
    quote_vendor_quote_number,--vendor_doc_num,
    from_header_id, --from_rfq_num,
    freight_terms_lookup_code,--freight_terms,
    fob_lookup_code,--fob,
    v_freight_H03,--freight_carrier,
    reply_method_lookup_code,--reply_method,
    note_to_vendor,
    quote_warning_delay,
    'INCOMPLETE',--approval_status,
    v_op_unit_H03,--attribute_category,
    null,--attribute1,
    null,--attribute2,
    null,--attribute3,
    po_header_id--, --attribute15,
--    SYSDATE, --LAST_UPDATE_DATE,
--    LAST_UPDATED_BY,
--    SYSDATE, --CREATION_DATE,
--    CREATED_BY,
--    LAST_UPDATE_LOGIN
FROM   po_headers_all
WHERE  po_header_id = po_header_rec.po_header_id;



begin
select   po,po_line, po_shipment
BULK COLLECT INTO v_gpd_array
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG IS NULL
and po=po_header_rec.po;
exception
when no_data_found then
null;
end;



FOR i IN v_gpd_array.first ..v_gpd_array.last
loop

--fnd_file.put_line(1,'after array loop');
 FOR po_det_rec IN po_det_cur (v_org_id_H03,po_header_rec.po_header_id,v_gpd_array(i).po_line,v_gpd_array(i).po_shipment,v_op_unit_N01)
  LOOP
--  fnd_file.put_line(1,'In loop');
  v_batch:=v_batch+1;

/********* Insert into Headers Interface Table ************/
/********* Insert into Lines Interface Table ************/
INSERT INTO po_lines_interface(interface_line_id,
       interface_header_id,
       action,
       ship_to_organization_id,
       ship_to_location_id,
       item_id,
       unit_price,
       unit_of_measure,
       document_num,
       item_description,
       line_num,
       min_order_quantity,
       max_order_quantity,
       vendor_product_num,
       note_to_vendor,
       un_number_id,
       quantity,
       price_discount,
       hazard_class_id,
       item_revision,
       line_type_id,
       --line_attribute1,
       line_attribute15,
       shipment_attribute_category,
       --effective_date,
       --                  expiration_date,
       ------- quantity_billed ,
       shipment_num,
       promised_date,
       need_by_date,
       note_to_receiver,
       tax_name,
       receive_close_tolerance,
       invoice_close_tolerance,
       days_early_receipt_allowed,
       days_late_receipt_allowed,
       receipt_days_exception_code,
       qty_rcv_tolerance,
       qty_rcv_exception_code,
       enforce_ship_to_location_code,
       shipment_attribute1,
       shipment_attribute2,
       shipment_attribute3,
       shipment_attribute4,
       shipment_attribute5,
       shipment_attribute6,
       SHIPMENT_ATTRIBUTE15--,
--                                                        LAST_UPDATE_DATE,
--                     LAST_UPDATED_BY,
--         CREATION_DATE,
--                     CREATED_BY,
--                     LAST_UPDATE_LOGIN
       )
SELECT po_lines_interface_s.NEXTVAL,
       v_po_header_interface_id,--    PO_HEADERS_INTERFACE_S.CURRVAL, --1369029
    'ADD',
    v_org_id_H03,
    v_ship_to_location_id,
    pol.item_id,
       pol.unit_price,
       pol.unit_meas_lookup_code,
       NULL,-----rec_quot_headers.segment1,
       pol.item_description,
       pol.line_num,
       pol.min_order_quantity,
       pol.max_order_quantity,
       pol.vendor_product_num,
       'Replacement of PO# '||po_header_rec.po||' Line# '||pol.line_num,----pol.note_to_vendor,
--       po_header_rec.po||'.'||pol.line_num ||' '|| 'Replaced',----pol.note_to_vendor,
       pol.un_number_id,
                                                        pol.quantity,
                                                        pll.price_discount,
       pol.hazard_class_id,
       pol.item_revision,
       pol.line_type_id,
       --'GEPSQUOTPOLINECONVADD',
                     pol.po_line_id,
                                                        v_op_unit_H03,
                                        --                SYSDATE,
--        SYSDATE+365,
                                                     -----   pol.quantity_billed ,
                   pll.shipment_num,
       pll.promised_date,
       pll.need_by_date,
       pll.note_to_receiver,
       pll.tax_name,
       pll.receive_close_tolerance,
       pll.invoice_close_tolerance,
       pll.days_early_receipt_allowed,
       pll.days_late_receipt_allowed,
       pll.receipt_days_exception_code,
       pll.qty_rcv_tolerance,
       pll.qty_rcv_exception_code,
       pll.enforce_ship_to_location_code,
       pll.attribute1,
       pll.attribute2,
       pll.attribute3,
       pll.attribute4,
       pll.attribute5,
       'MANUAL',--pll.attribute6,
       pll.LINE_LOCATION_ID--,
--                                                         SYSDATE,
--                        pol.last_updated_by,
--                     SYSDATE,
--                     pol.created_by,
--                     pol.last_update_login
FROM  po_lines_all pol, po_line_locations_all pll
WHERE  pol.po_line_id = pll.po_line_id
AND  pll.line_location_id = po_det_rec.line_location_id;
--  fnd_file.put_line(1,'distributions int loop');
/********* Insert into Lines Interface Table ************/
/********* Insert into Distributions Interface Table ************/
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
              (INTERFACE_HEADER_ID,
                                          INTERFACE_LINE_ID,
                                          INTERFACE_DISTRIBUTION_ID,
                                          PO_HEADER_ID,
                                          PO_LINE_ID,
                                          DISTRIBUTION_NUM,
                                          ORG_ID,
                                          QUANTITY_ORDERED,
                                          CHARGE_ACCOUNT_ID,
                                          QUANTITY_DELIVERED,
                            QUANTITY_BILLED,
                            QUANTITY_CANCELLED,
                      ----      DESTINATION_TYPE_CODE,
                    -----        DESTINATION_SUBINVENTORY,
                            REQ_HEADER_REFERENCE_NUM,
                            REQ_LINE_REFERENCE_NUM,
                            RATE_DATE,
       attribute1,
--                                          LAST_UPDATE_DATE,
--       LAST_UPDATED_BY,
--         CREATION_DATE,
--       CREATED_BY,
--       LAST_UPDATE_LOGIN,
attribute15)
SELECT v_po_header_interface_id,--PO_HEADERS_INTERFACE_S.CURRVAL,
                                     po_lines_interface_s.CURRVAL,
                                     PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL,
                                     po_header_id,
                                     po_line_id,
                                     DISTRIBUTION_NUM,
                                     v_op_unit_H03,
                                     QUANTITY_ORDERED,
                                     null,--code_combination_id,
                                     QUANTITY_DELIVERED,
                                     QUANTITY_BILLED,
                          QUANTITY_CANCELLED,
   --------      rec_disttribution.DESTINATION_TYPE_CODE,
   ---------      rec_disttribution.DESTINATION_SUBINVENTORY,
                                     REQ_HEADER_REFERENCE_NUM,
          REQ_LINE_REFERENCE_NUM,
                      RATE_DATE,
         attribute1,
--                                     SYSDATE,
--                          last_updated_by,
--                      SYSDATE,
--                       created_by,
--                       last_update_login,
        PO_DISTRIBUTION_ID
FROM  po_distributions_all
WHERE po_header_id  = po_header_rec.po_header_id
AND  po_line_id  = po_det_rec.po_line_id;
/********* Insert into Distributions Interface Table ************/
   begin
        UPDATE GEPS_PO_DETAILS
        SET po_transfer_flag = 'SUCCESS'
    ,po_header_id = po_header_rec.po_header_id
        , po_line_id = po_det_rec.po_line_id
        , line_location_id = po_det_rec.line_location_id
        WHERE po   = po_header_rec.po
    AND po_line  = po_det_rec.po_line
    AND po_shipment  = po_det_rec.po_shipment;
   exception
   when others then
   null;
   fnd_file.put_line(fnd_file.LOG, 'when others then PO#'||po_header_rec.po||' Line # '||po_det_rec.po_line||' Shipment '||po_det_rec.po_shipment);
   fnd_file.put_line(fnd_file.LOG, 'PO Header ID '||po_header_rec.po_header_id||' Line ID '||po_det_rec.po_line_id||' Line Location ID '||po_det_rec.line_location_id);
   end;

   if v_batch=1000 then
   commit;
   v_batch:=0;
   end if;
  END LOOP;
--  fnd_file.put_line(1,'distributions int loop');
END LOOP;--END OF v_gpd_array LOOP
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
     fnd_file.put_line(fnd_file.LOG, 'Vendor Site Validation Failed for New Org');
     UPDATE geps_po_details
     SET po_transfer_flag= 'E'
        , error_msg  = 'Vendor Site : '||po_header_rec.vendor_site_code||' not found'
     WHERE po   = po_header_rec.po;
   END;
exception
       WHEN no_data_found THEN
     fnd_file.put_line(fnd_file.LOG, 'Error occured for sequence PO_HEADERS_INTERFACE_S.NEXTVAL ');
     UPDATE geps_po_details
     SET po_transfer_flag= 'E'
        , error_msg  = 'Error occured for sequence PO_HEADERS_INTERFACE_S.NEXTVAL '
     WHERE po   = po_header_rec.po;
    end;
end loop;--po_header loop
 COMMIT;

    v_request_id := fnd_request.submit_request( 'PO'
        ,'POXPOPDOI'
        ,''
       ,SYSDATE
        ,FALSE
       ,NULL
        ,'STANDARD'
        ,''
        ,'N'
        ,NULL
        ,'INCOMPLETE'
        ,NULL
        ,V_DATE
        ,v_op_unit_H03
        ,NULL);
 COMMIT;
-- G_POXPOPDOI_REQUEST_ID:=v_request_id;

 l_bool_return := FND_CONCURRENT.WAIT_FOR_REQUEST(v_request_id
                                                 ,5
                                                 ,10000
                                                 ,l_phase
                                                 ,l_status
                                                 ,l_dev_phase
                                                 ,l_dev_status
                                                 ,l_message);
 IF l_bool_return  THEN
             IF (l_dev_phase = 'COMPLETE') THEN
                  IF (l_dev_status = 'NORMAL') THEN
                       FND_FILE.PUT_LINE(FND_FILE.LOG,'Request '||l_request_number||' Completed Normal...');
                       l_retun_flag :='S';
                  ELSIF (l_dev_status = 'ERROR') THEN
                      Fnd_File.PUT_LINE(Fnd_File.LOG,'Request '||l_request_number||' Completed Error...');
                      l_retun_flag :='E';
    ELSIF (l_dev_status = 'WARNING') THEN
                      Fnd_File.PUT_LINE(Fnd_File.LOG,'Request '||l_request_number||' Completed Warning...');
                      l_retun_flag :='W';
    END IF;
              END IF;
  END IF;
  GEPS_UPDATE_STAGING(p_from_po,p_to_po,v_request_id);
--  GEPS_ERROR_RPT;

exception
when others then
fnd_file.put_line(1,'not working'||sqlerrm);
END GEPS_PO_PRC;


 PROCEDURE GEPS_UPDATE_STAGING(p_from_po varchar2,p_to_po varchar2,p_request_id number)
 IS
 --cursor to pick up rec which are validated
    CURSOR cur_po_det
    IS SELECT ROWID,po,po_line, po_shipment, po_header_id,  po_line_id,  line_location_id
 FROM geps_po_details
 WHERE PO_TRANSFER_FLAG='SUCCESS'
 and H03_PO_HEADER_ID is null
 and po between p_from_po and p_to_po;
 --cursor to tke up n01 PO data
  /*  CURSOR cur_lines_det(c_po VARCHAR2,c_PO_LINE NUMBER,c_PO_SHIPMENT NUMBER)
    IS
 SELECT pll.LINE_LOCATION_ID, pll.PO_LINE_ID
 FROM
     PO_HEADERS_ALL PHA,
     PO_LINES_ALL PLA ,
     PO_LINE_LOCATIONS_ALL PLL
 WHERE pha.segment1=c_po
 AND pha.po_header_id=pla.po_header_id
 AND pla.LINE_NUM=c_PO_LINE
 AND pll.po_header_id= pha.po_header_id
 AND pll.PO_LINE_ID=pla.PO_LINE_ID
 AND PLL.SHIPMENT_NUM=c_PO_SHIPMENT;*/
v_sql_errm    VARCHAR2(128);
v_line_count  NUMBER:=0;
sucess_flag  NUMBER;
v_h03_line_location_id NUMBER;
v_h03_po_line_id NUMBER;
v_h03_po_header_id NUMBER;
v_h03_po NUMBER;
v_h03_line NUMBER;
V_N01_NOTE_TO_VENDOR VARCHAR2(240);
v_batch number;
v_conc_req_start_time Date;
BEGIN
v_batch:=0;
BEGIN
select ACTUAL_START_DATE
INTO  v_conc_req_start_time
from fnd_concurrent_requests
where request_id=p_request_id;
exception
WHEN others THEN
NULL;
END;
   FOR rec_cur_po_det IN cur_po_det
   LOOP
   v_batch:=v_batch+1;
--      FOR rec_cur_lines_det IN cur_lines_det(rec_cur_po_det.PO,rec_cur_po_det.PO_LINE,rec_cur_po_det.PO_SHIPMENT)
--      LOOP
 --FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO||'LINE:'||rec_cur_po_det.PO_LINE||'SHIPment:'||rec_cur_po_det.PO_SHIPMENT);
 --check the POLLA table where attribute15 is the N01 PO Line Id.
-- fnd_file.put_line(1,'line location_id :' ||rec_cur_po_det.LINE_LOCATION_ID);
-- fnd_file.put_line(1,'v_conc_req_start_time :'||v_conc_req_start_time);
--fnd_file.put_line(1,'v_conc_req_start_time :'||to_char(v_conc_req_start_time,'DD-MON-YYYY HH24:MI:SS'));

 BEGIN
 SELECT line_location_id , po_line_id, po_header_id
 INTO v_h03_line_location_id,v_h03_po_line_id,v_h03_po_header_id
 FROM PO_LINE_LOCATIONS_ALL
 WHERE attribute15=rec_cur_po_det.LINE_LOCATION_ID
 And last_update_date>v_conc_req_start_time
 AND ROWNUM=1;
    sucess_flag:=1;
       FND_FILE.PUT_LINE(FND_FILE.LOG,v_h03_line_location_id||'.'||v_h03_po_line_id||'.'||v_h03_po_header_id );
 EXCEPTION
 WHEN NO_DATA_FOUND THEN
-- fnd_file.put_line(1,'no data found');
 sucess_flag:=0;
 v_h03_line_location_id:=NULL;
 v_h03_po_line_id:=NULL;
 v_h03_po_header_id:=NULL;
    WHEN OTHERS THEN
--     fnd_file.put_line(1,'no others');
 sucess_flag:=0;
  v_h03_line_location_id:=NULL;
 v_h03_po_line_id:=NULL;
 v_h03_po_header_id:=NULL;
 END;
-- fND_FILE.PUT_LINE(FND_FILE.LOG,'po created ');
 IF sucess_flag >0 THEN
    --update the N01 PO line with note to vendor as H03 PO# . Line#
--    fND_FILE.PUT_LINE(FND_FILE.LOG,'cancel the po');
       BEGIN
    SELECT poha.segment1,pola.line_num
    INTO v_h03_po, v_h03_line
    FROM po_headers_all poha, po_lines_all pola
    WHERE poha.po_header_id=v_h03_po_header_id
    AND pola.po_line_id=v_h03_po_line_id
    AND poha.po_header_id=pola.po_header_id;
    EXCEPTION
    WHEN OTHERS THEN
    v_h03_po:=NULL;
    v_h03_line:=NULL;
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while finding v_h03_po Po:'||SQLERRM);
    END;
           FND_FILE.PUT_LINE(FND_FILE.LOG,v_h03_po||'.'||v_h03_line );
    v_n01_note_to_vendor:='Replaced by PO# '||v_h03_po||' Line# '||v_h03_line;
               FND_FILE.PUT_LINE(FND_FILE.LOG,v_n01_note_to_vendor);
    BEGIN
 FOR po_rec IN(
 SELECT po.ROWID, po.*
    FROM   PO_LINES po
    WHERE  po.po_line_id = rec_cur_po_det.po_line_id) LOOP
   PO_LINES_PKG_SUD.UPDATE_ROW
        ( X_Rowid                          => po_rec.ROWID
        , X_Po_Line_Id                     => po_rec.Po_Line_Id
        , X_Last_Update_Date               => SYSDATE
        , X_Last_Updated_By                => FND_GLOBAL.USER_ID
        , X_Po_Header_Id                   => po_rec.Po_Header_Id
        , X_Line_Type_Id                   => po_rec.Line_Type_Id
        , X_Line_Num                       => po_rec.Line_Num
        , X_Last_Update_Login              => FND_GLOBAL.LOGIN_ID
        , X_Item_Id                        => po_rec.Item_Id
        , X_Item_Revision                  => po_rec.Item_Revision
        , X_Category_Id                    => po_rec.Category_Id
        , X_Item_Description               => po_rec.Item_Description
        , X_Unit_Meas_Lookup_Code          => po_rec.Unit_Meas_Lookup_Code
        , X_Quantity_Committed             => po_rec.Quantity_Committed
        , X_Committed_Amount               => po_rec.Committed_Amount
        , X_Allow_Price_Override_Flag      => po_rec.Allow_Price_Override_Flag
        , X_Not_To_Exceed_Price            => po_rec.Not_To_Exceed_Price
        , X_List_Price_Per_Unit            => po_rec.List_Price_Per_Unit
        , X_Unit_Price                     => po_rec.Unit_Price
        , X_Quantity                       => po_rec.Quantity
        , X_Un_Number_Id                   => po_rec.Un_Number_Id
        , X_Hazard_Class_Id                => po_rec.Hazard_Class_Id
        , X_Note_To_Vendor                 => v_n01_note_to_vendor-----<<<------
        , X_From_Header_Id                 => po_rec.From_Header_Id
        , X_From_Line_Id                   => po_rec.From_Line_Id
        , X_Min_Order_Quantity             => po_rec.Min_Order_Quantity
        , X_Max_Order_Quantity             => po_rec.Max_Order_Quantity
        , X_Qty_Rcv_Tolerance              => po_rec.Qty_Rcv_Tolerance
        , X_Over_Tolerance_Error_Flag      => po_rec.Over_Tolerance_Error_Flag
        , X_Market_Price                   => po_rec.Market_Price
        , X_Unordered_Flag                 => po_rec.Unordered_Flag
        , X_Closed_Flag                    => po_rec.Closed_Flag
        , X_User_Hold_Flag                 => po_rec.User_Hold_Flag
        , X_Cancel_Flag                    => po_rec.Cancel_Flag
        , X_Cancelled_By                   => po_rec.Cancelled_By
        , X_Cancel_Date                    => po_rec.Cancel_Date
        , X_Cancel_Reason                  => po_rec.Cancel_Reason
        , X_Firm_Status_Lookup_Code        => po_rec.Firm_Status_Lookup_Code
        , X_Firm_Date                      => po_rec.Firm_Date
        , X_Vendor_Product_Num             => po_rec.Vendor_Product_Num
        , X_Contract_Num                   => po_rec.Contract_Num
        , X_Taxable_Flag                   => po_rec.Taxable_Flag
        , X_Tax_Code_Id                    => po_rec.Tax_Code_Id
        , X_Type_1099                      => po_rec.Type_1099
        , X_Capital_Expense_Flag           => po_rec.Capital_Expense_Flag
        , X_Negotiated_By_Preparer_Flag    => po_rec.Negotiated_By_Preparer_Flag
        , X_Attribute_Category             => po_rec.Attribute_Category
        , X_Attribute1                     => po_rec.Attribute1
        , X_Attribute2                     => po_rec.Attribute2
        , X_Attribute3                     => po_rec.Attribute3
        , X_Attribute4                     => po_rec.Attribute4
        , X_Attribute5                     => po_rec.Attribute5
        , X_Attribute6                     => po_rec.Attribute6
        , X_Attribute7                     => po_rec.Attribute7
        , X_Attribute8                     => po_rec.Attribute8
        , X_Attribute9                     => po_rec.Attribute9
        , X_Attribute10                    => po_rec.Attribute10
        , X_Reference_Num                  => po_rec.Reference_Num
        , X_Attribute11                    => po_rec.Attribute11
        , X_Attribute12                    => po_rec.Attribute12
        , X_Attribute13                    => po_rec.Attribute13
        , X_Attribute14                    => po_rec.Attribute14
        , X_Attribute15                    => po_rec.Attribute15
        , X_Min_Release_Amount             => po_rec.Min_Release_Amount
        , X_Price_Type_Lookup_Code         => po_rec.Price_Type_Lookup_Code
        , X_Closed_Code                    => po_rec.Closed_Code
        , X_Price_Break_Lookup_Code        => po_rec.Price_Break_Lookup_Code
        , X_Ussgl_Transaction_Code         => po_rec.Ussgl_Transaction_Code
        , X_Government_Context             => po_rec.Government_Context
        , X_Closed_Date                    => po_rec.Closed_Date
        , X_Closed_Reason                  => po_rec.Closed_Reason
        , X_Closed_By                      => po_rec.Closed_By
        , X_Transaction_Reason_Code        => po_rec.Transaction_Reason_Code
        , X_Global_Attribute_Category      => po_rec.Global_Attribute_Category
        , X_Global_Attribute1              => po_rec.Global_Attribute1
        , X_Global_Attribute2              => po_rec.Global_Attribute2
        , X_Global_Attribute3              => po_rec.Global_Attribute3
        , X_Global_Attribute4              => po_rec.Global_Attribute4
        , X_Global_Attribute5              => po_rec.Global_Attribute5
        , X_Global_Attribute6              => po_rec.Global_Attribute6
        , X_Global_Attribute7              => po_rec.Global_Attribute7
        , X_Global_Attribute8              => po_rec.Global_Attribute8
        , X_Global_Attribute9              => po_rec.Global_Attribute9
        , X_Global_Attribute10             => po_rec.Global_Attribute10
        , X_Global_Attribute11             => po_rec.Global_Attribute11
        , X_Global_Attribute12             => po_rec.Global_Attribute12
        , X_Global_Attribute13             => po_rec.Global_Attribute13
        , X_Global_Attribute14             => po_rec.Global_Attribute14
        , X_Global_Attribute15             => po_rec.Global_Attribute15
        , X_Global_Attribute16             => po_rec.Global_Attribute16
        , X_Global_Attribute17             => po_rec.Global_Attribute17
        , X_Global_Attribute18             => po_rec.Global_Attribute18
        , X_Global_Attribute19             => po_rec.Global_Attribute19
        , X_Global_Attribute20             => po_rec.Global_Attribute20
        , X_Expiration_Date                => po_rec.Expiration_Date
        , X_Base_Uom                       => po_rec.Base_Uom
        , X_Base_Qty                       => po_rec.Base_Qty
        , X_Secondary_Uom                  => po_rec.Secondary_Uom
        , X_Secondary_Qty                  => po_rec.Secondary_Qty
        , X_Qc_Grade                       => po_rec.Qc_Grade
        , P_MANUAL_PRICE_CHANGE_FLAG       => po_rec.MANUAL_PRICE_CHANGE_FLAG
        );
 END LOOP;
-- COMMIT;
    --Geps_Po_Approval_Pkg.GEPS_PO_UPDATE_LINE(rec_cur_lines_det.po_line_id, v_n01_note_to_vendor);
    EXCEPTION
    WHEN OTHERS THEN
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating the n01 po lines:'||SQLERRM);
    END;
     BEGIN
            UPDATE geps_po_details
           SET  PO_TRANSFER_FLAG='SUCCESS', comments=v_n01_note_to_vendor
            ,H03_PO_HEADER_ID=v_h03_po_header_id,H03_PO_LINE_ID=v_h03_po_line_id
            WHERE ROWID=rec_cur_po_det.ROWID;
                COMMIT;
          EXCEPTION WHEN OTHERS THEN
                v_sql_errm := SQLERRM;
  FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO);
          END;
ELSE
 BEGIN
          UPDATE geps_po_details
          SET  PO_TRANSFER_FLAG='FAILED'
    WHERE ROWID=rec_cur_po_det.ROWID;
          COMMIT;
          EXCEPTION WHEN OTHERS THEN
          v_sql_errm := SQLERRM;
  FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while updating Po:'||rec_cur_po_det.PO||'LINE:'||rec_cur_po_det.PO_LINE||'SHIPment:'||rec_cur_po_det.PO_SHIPMENT);
          END;
END IF;
--         END LOOP;
if v_batch=1000 then
commit;
v_batch:=0;
end if;
       END LOOP;
commit;
END GEPS_UPDATE_STAGING;
--+--------------------------------------------------------------------------------------------------------------------------+--


PROCEDURE GEPS_ERROR_RPT IS

CURSOR cur_header is
select distinct INTERFACE_HEADER_ID
from po_headers_interface
where batch_id in (g_batch_id1,g_batch_id2,g_batch_id3,g_batch_id4)
and process_code<>'ACCEPTED';

CURSOR cur_quot_err(p_interface_header_id  number) IS
SELECT segment1
FROM   po_headers_all
WHERE  po_header_id in
(select po_header_id from  po_lines_all where po_line_id in
(select Line_attribute15 from po_lines_interface where INTERFACE_HEADER_ID = p_interface_header_id));


 CURSOR cur_header_err(p_interface_header_id  number)
 IS
       SELECT distinct COLUMN_NAME,ERROR_MESSAGE
       FROM po_interface_errors
       WHERE INTERFACE_HEADER_ID =p_interface_header_id;

cursor cur_custom_error is
select  po,error_msg
from geps_po_details
where po_transfer_flag= 'E';

cursor cur_success_po is
select distinct po,po_line,comments
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='SUCCESS';

v_total           number;
v_success_total   number;
v_custom_count    number;
v_interface_count number;
BEGIN
select count(1)
into v_total
from GEPS_PO_DETAILS;
fnd_file.put_line(2,'Total Number of records in staging table '||v_total);
fnd_file.put_line(2,' ');

select count(1)
into v_success_total
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='SUCCESS';
fnd_file.put_line(2,'Total Number of records successfully processed '||v_success_total);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records successfully processed are'||v_success_total);
for rec_success_po in cur_success_po
loop
fnd_file.put_line(2,'N02 po# ' ||rec_success_po.po||' N02 Line num '||rec_success_po.po_line ||' '||rec_success_po.comments);
end loop;-- end of rec_success_po



select count(1)
into v_custom_count
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='E';
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Number of records failed due to custom validation '||v_custom_count);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records failed due to custom validation are');

for rec_custom_error in cur_custom_error
loop
fnd_file.put_line(2,'PO# '||rec_custom_error.po||'Error '||rec_custom_error.error_msg);
end loop; --end of rec_custom_error

select count(1)
into v_interface_count
from GEPS_PO_DETAILS
where PO_TRANSFER_FLAG='FAILED';
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Number of records failed in interface table '||v_interface_count);
fnd_file.put_line(2,' ');
fnd_file.put_line(2,'Records failed in interface table are ');
for rec_header in cur_header
loop
for rec_quot_err in cur_quot_err(rec_header.interface_header_id)
loop
for rec_header_err in cur_header_err(rec_header.interface_header_id)
loop
fnd_file.put_line(2,'PO# '||rec_quot_err.segment1||' COLUMN '||rec_header_err.COLUMN_NAME||'Error '||rec_header_err.ERROR_MESSAGE);
end loop;-- end of rec_header_err
end loop;-- end of rec_quot_err
end loop; ---end of rec_header
END GEPS_ERROR_RPT;

procedure geps_process_po(ERRBUF OUT VARCHAR,RETCODE OUT NUMBER,p_agent_id number)
IS
   TYPE rec_po IS RECORD
    ( po                 GEPS_PO_DETAILS.po%type
    );
 TYPE p_po_tbl_type IS TABLE OF rec_po INDEX BY BINARY_INTEGER;
 v_po_array p_po_tbl_type;

--   v_po_array               p_inventory_item_tbl_type;
--   v_gim_id mtl_parameters.organization_id%TYPE;
   v_break number;
   v_break_first number;
   v_break_last number:=0;
   from_po GEPS_PO_DETAILS.po%type;
   to_po GEPS_PO_DETAILS.po%type;
   v_resp_appl_id      NUMBER := FND_PROFILE.VALUE('RESP_APPL_ID');
   v_resp_id           NUMBER := FND_PROFILE.VALUE('RESP_ID');
   v_user_id           NUMBER := FND_PROFILE.VALUE('USER_ID');
   v_request1          number;
   v_request2          number;
   v_request3          number;
   v_request4          number;

   V_INTERVAL          NUMBER := 3; --seconds
   V_PHASE             VARCHAR2 (240);
   V_STATUS            VARCHAR2 (240);
   V_REQUEST_PHASE     VARCHAR2 (240);
   V_REQUEST_STATUS    VARCHAR2 (240);
   V_FINISHED          BOOLEAN;
   V_MESSAGE           VARCHAR2 (1000);
--   v_number_records number;
   v_agent_id number;

BEGIN

v_agent_id:=p_agent_id;

FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id,v_resp_appl_id);

begin
execute immediate 'analyze table apps.geps_po_details compute statistics';
exception
when others then
null;
end;

   g_batch_id1:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||1);
   g_batch_id2:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||2);
   g_batch_id3:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||3);
   g_batch_id4:=to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||4);

begin
select distinct po
BULK COLLECT INTO v_po_array
from GEPS_PO_DETAILS
where  PO_TRANSFER_FLAG IS NULL
order by po;
exception
when no_data_found then
null;
end;


--fnd_file.put_line(1,' No of records : '||v_inventory_item_array.last);

if (v_po_array.last)<4 br="br" then="then">   v_break_first:=v_po_array.first;
   from_po:=v_po_array(v_break_first).po;
   v_break_last:=v_po_array.last;
   to_po:=v_po_array(v_break_last).po;
else
   v_break :=floor(v_po_array.last/4);
--   fnd_file.put_line(1,' No of v_break  records : '||v_break );


   v_break_first:=v_break_last+1;
   from_po:=v_po_array(v_break_first).po;
   v_break_last:=v_break_last+v_break;
   to_po:=v_po_array(v_break_last).po;
end if;
--fnd_file.put_line(1,' from_po : '||from_po);
--fnd_file.put_line(1,' to_po : '||to_po);
--to_number(TO_CHAR(SYSDATE,'DDMMYYYYHH24MISS')||1);
v_request1:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
           '',
           '',
           FALSE,
           from_po, to_po,g_batch_id1,v_agent_id);
commit;
--fnd_file.put_line(1,' from_po : '||from_po);
--fnd_file.put_line(1,' to_po : '||to_po);

if (v_po_array.last)>4 then
   v_break_first:=v_break_last+1;
   from_po:=v_po_array(v_break_first).po;
   v_break_last:=v_break_last+v_break;
   to_po:=v_po_array(v_break_last).po;
   v_request2:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
              '',
              '',
              FALSE,
              from_po, to_po,g_batch_id2,v_agent_id);
   commit;
--   fnd_file.put_line(1,' request id : '||v_request2);

   v_break_first:=v_break_last+1;
   from_po:=v_po_array(v_break_first).po;
   v_break_last:=v_break_last+v_break;
   to_po:=v_po_array(v_break_last).po;
   v_request3:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
              '',
              '',
              FALSE,
              from_po, to_po,g_batch_id3,v_agent_id);

   commit;
--   fnd_file.put_line(1,' request id : '||v_request3);

   v_break_first:=v_break_last+1;
   from_po:=v_po_array(v_break_first).po;
   v_break_last:=v_po_array.last;
   to_po:=v_po_array(v_break_last).po;
   v_request4:=FND_REQUEST.SUBMIT_REQUEST('GEPSPO','GEPS_PURCHASE_CONV',
              '',
              '',
              FALSE,
              from_po, to_po,g_batch_id4,v_agent_id);
   commit;
--   fnd_file.put_line(1,' request id : '||v_request4);
--  fnd_file.put_line(1,' to_po : '||to_po);
end if;


IF v_request1 =0 THEN
  FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PLANNER PROCESS SUBMISSION IS FAILED ');
--  END IF;
ELSE
   V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
               (REQUEST_ID      => v_request1,
                INTERVAL        => V_INTERVAL,
                MAX_WAIT        => 0,
                PHASE           => V_PHASE,
                STATUS          => V_STATUS,
                DEV_PHASE       => V_REQUEST_PHASE,
                DEV_STATUS      => V_REQUEST_STATUS,
                MESSAGE         => V_MESSAGE);
   IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
      FND_FILE.PUT_LINE (FND_FILE.LOG,            'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....'        || '  '|| TO_CHAR (v_request1));
 --  END;
   ELSE
       FND_FILE.PUT_LINE (FND_FILE.LOG,           'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM  COMPLETED WITH STATUS '            ||V_REQUEST_STATUS||'  '|| V_MESSAGE|| '  '|| TO_CHAR (v_request1));
   END IF;
end if;


if (v_po_array.last)>4 then
   IF v_request2 =0 THEN
     FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
   --  END IF;
   ELSE
      V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
                  (REQUEST_ID      => v_request2,
                   INTERVAL        => V_INTERVAL,
                   MAX_WAIT        => 0,
                   PHASE           => V_PHASE,
                   STATUS          => V_STATUS,
                   DEV_PHASE       => V_REQUEST_PHASE,
                   DEV_STATUS      => V_REQUEST_STATUS,
                   MESSAGE         => V_MESSAGE);
      IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG,            'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....'        || '  '|| TO_CHAR (v_request2));
    --  END;
      ELSE
          FND_FILE.PUT_LINE (FND_FILE.LOG,           'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM  COMPLETED WITH STATUS '            ||V_REQUEST_STATUS||'  '|| V_MESSAGE|| '  '|| TO_CHAR (v_request2));
      END IF;
   end if;

   IF v_request3 =0 THEN
     FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
   --  END IF;
   ELSE
      V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
                  (REQUEST_ID      => v_request3,
                   INTERVAL        => V_INTERVAL,
                   MAX_WAIT        => 0,
                   PHASE           => V_PHASE,
                   STATUS          => V_STATUS,
                   DEV_PHASE       => V_REQUEST_PHASE,
                   DEV_STATUS      => V_REQUEST_STATUS,
                   MESSAGE         => V_MESSAGE);
      IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG,            'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....'        || '  '|| TO_CHAR (v_request3));
    --  END;
      ELSE
          FND_FILE.PUT_LINE (FND_FILE.LOG,           'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM  COMPLETED WITH STATUS '            ||V_REQUEST_STATUS||'  '|| V_MESSAGE|| '  '|| TO_CHAR (v_request3));
      END IF;
   end if;

   IF v_request4 =0 THEN
     FND_FILE.PUT_LINE(FND_FILE.LOG, 'GEPS PURCHASE ORDERS CONVERSION SUBMISSION IS FAILED ');
   --  END IF;
   ELSE
      V_FINISHED :=FND_CONCURRENT.WAIT_FOR_REQUEST
                  (REQUEST_ID      => v_request4,
                   INTERVAL        => V_INTERVAL,
                   MAX_WAIT        => 0,
                   PHASE           => V_PHASE,
                   STATUS          => V_STATUS,
                   DEV_PHASE       => V_REQUEST_PHASE,
                   DEV_STATUS      => V_REQUEST_STATUS,
                   MESSAGE         => V_MESSAGE);
      IF (UPPER(V_REQUEST_PHASE) = 'COMPLETE' AND UPPER(V_REQUEST_STATUS) = 'NORMAL') THEN
         FND_FILE.PUT_LINE (FND_FILE.LOG,            'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION CONCURRENT REQUEST COMPLETED SUCCESSFULLY ....'        || '  '|| TO_CHAR (v_request4));
    --  END;
      ELSE
          FND_FILE.PUT_LINE (FND_FILE.LOG,           'SUBMISSION OF GEPS PURCHASE ORDERS CONVERSION PROGRAM  COMPLETED WITH STATUS '            ||V_REQUEST_STATUS||'  '|| V_MESSAGE|| '  '|| TO_CHAR (v_request4));
      END IF;
   end if;
END IF;
--calling or error or sucess report;
GEPS_ERROR_RPT;
end GEPS_PROCESS_PO;


END GEPS_PURCHASE_CONV;
/


 



No comments:

Post a Comment

Post a Comment