anonymous block for customer creaton organization type

-----------******anonymous block for customer creaton organization type*******----------------

declare
  p_organization_rec   hz_party_v2pub.organization_rec_type;
  x_return_status varchar2(200);
  x_msg_count number;
  x_msg_data varchar2(200);
  x_party_id number;
  x_party_number number;
  x_profile_id number;
begin
p_organization_rec.organization_name :='xx_org_rel_test123';
  p_organization_rec.created_by_module :='TCA_FORM_WRAPPER';
  hz_party_v2pub.create_organization ('T',
  p_organization_rec,
  x_return_status,
  x_msg_count,
  x_msg_data,
  x_party_id,
  x_party_number,
  x_profile_id );
  IF  x_return_status ='S' THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer Profile is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust party Id           = '||TO_CHAR(x_party_id));
    dbms_output.put_line('Cust party number       = '||TO_CHAR(x_party_number));
    dbms_output.put_line('Cust profile Id         = '||x_profile_id);
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Profile got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;

anonymous block for customer creation as person type

---------*********anonymous block for customer creation as person type********--------------------------


declare
  p_person_rec   hz_party_v2pub.person_rec_type;
  x_return_status varchar2(200);
  x_msg_count number;
  x_msg_data varchar2(200);
  x_party_id number;
  x_party_number number;
  x_profile_id number;
begin
p_person_rec.person_first_name :='xx_rel_test1';
p_person_rec.person_last_name :='xx_rel_test2';
  p_person_rec.created_by_module :='HZ_CPUI';
  hz_party_v2pub.create_person (
   'T',
    p_person_rec               ,
    x_party_id                 ,
    x_party_number             ,
    x_profile_id               ,
    x_return_status            ,
    x_msg_count                ,
    x_msg_data                
);
  IF  x_return_status ='S' THEN
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Creation of Customer Profile is Successful ');
    DBMS_OUTPUT.PUT_LINE('Output information ....');  
    dbms_output.put_line('Cust party Id           = '||TO_CHAR(x_party_id));
    dbms_output.put_line('Cust party number       = '||TO_CHAR(x_party_number));
    dbms_output.put_line('Cust profile Id         = '||x_profile_id);
    dbms_output.put_line('Return Status Of Api    = '||x_return_status);
    dbms_output.put_line('No of err msgs          = '||x_msg_count);
    dbms_output.put_line('err information          = '||x_msg_data);
ELSE
    DBMS_OUTPUT.put_line ('Creation of Customer Profile got failed:'||x_msg_data);
    ROLLBACK;
    FOR i IN 1 .. x_msg_count
    LOOP
      x_msg_data := fnd_msg_pub.get( p_msg_index => i, p_encoded => 'F');
      dbms_output.put_line( i|| ') '|| x_msg_data);
    END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('Completion of API');
end;

 

Statuses of Normal Order and Drop shipment and Back to back orders in Order management from Oracle Apps

*****************************NORMAL SALES ORDER STATUSES********************

STEP1:-ENTER THE DETAILS THEN SAVE
   
       HEADER STATUS:--ENTERED
       LINE STATUS  :--ENTERED

STEP2:-BOOK THE ORDER

       HEADER STATUS:--BOOKED
       LINE STATUS  :--AWAITING SHIPPING

STEP3:-GOTO SHIPPABLE->TRANSACTIONS
                     ->LINES/LPN       ACTIONS:AUTO CREATE DELIVERY-->GO
                     ->DELIVERY        ACTIONS:LAUNCH PICK RELEASE -->GO-->OK
                     ->REQUESTS->FIND-->PICK SELECTION GENERATION
                                        PICK SLIP RTEPORT
                                        SHIPPING EXCEPTION REPORT

       HEADER STATUS:--BOOKED
       LINE STATUS  :--PICKED

STEP4:-GOTO SHIPPING->TRANSACTIONS
                     ->DELIVERY        ACTIONS:SHIP CONFORM-->GO--OK
                     ->REQUESTS->FIND-->BILL OF LOADING
                                        PACKING SHIP REPORT
                                        COMMERCIAL INVOICE
                                        VEHICLE LOAD SHETT DETAILS
                                        INTERFACE TRIP STAFF
       HEADER STATUS:--BOOKED
       LINE STATUS  :--SHIPPED

STEP5:-NOW RUN THE WORKFLOW BACKGROUND PROCESS
                       ITEMTYPE:-OM ORDER LINE

       HEADER STATUS:--BOOKED
       LINE STATUS  :--CLOSED
----------------------------------------------------------------
HEADER STATUS:-ENTERED        LINE STATUS:-ENTERED
HEADER STATUS:-BOOKED         LINE STATUS:-AWAITING SHIPPING
HEADER STATUS:-BOOKED         LINE STATUS:-PICKED
HEADER STATUS:-BOOKED         LINE STATUS:-SHIPPED
HEADER STATUS:-BOOKED         LINE STATUS:-CLOSED
----------------------------------------------------------------

***********************DROP SHIPMENT STATUSES***************************

STEP1:-ENTER THE DETAILS THEN SAVE(IN SHIPPING TAB SOURCETYPE=EXTERNAL)
   
       HEADER STATUS:--ENTERED
       LINE STATUS  :--ENTERED

STEP2:-BOOK THE ORDER

       HEADER STATUS:--BOOKED
       LINE STATUS  :--BOOKED

STEP3:-NOW RUN THE WORKFLOW BACKGROUND PROCESS(PARAM:ITEMTYPE NULL,YES,YES)

       HEADER STATUS:--BOOKED
       LINE STATUS  :--Awaiting Receipt
       (THE DETAILS MUST BE PRESENT IN REQUISITION INTEFACE TABLE)

STEP4:-NOW RUN THE REQUISITION IMPORT(PARAM:ITEMTYPE=ORDER ENTRY,ITEM,NO,YES)
       TO LOAD THE DATA INTO REQUISITION BASE TABLES

       HEADER STATUS:--BOOKED
       LINE STATUS  :--Awaiting Receipt
       (THE DETAILS MUST BE PRESENT IN REQUISITION BASE TABLE)

STEP5:-NOW COPY THE SALES ORDER NUMBER
       GOTO-->PURCHASING-->REQUISITION SUMMAY->RELATEC DOCUMENTS->SALES ORDER               NUMBER->FIND->COPY REQUISITION NUMBER

       GOTO-->PURCHASE ORDERS->AUTO CREATE->DELETE SEARCH CRITIRIAA->ENTER REQUISITION             NUMBER->AUTOMETIC->ENTER SUPPLIER NAME->CREATE->PURCHASE ORDER WILL BE               CREATED->COPY PO NUM&APPROVE THE PURCHASE ORDER

       GOTO-->RECIEVING->RECIEPTS->ENTER PONUM->FIND->SUB:STORES->SAVE

       HEADER STATUS:--BOOKED
       LINE STATUS  :--SHIPPED


STEP6:--NOW RUN THE WORKFLOW BACKGROUND PROCESS
           
       HEADER STATUS:--BOOKED
       LINE STATUS  :--CLOSED
----------------------------------------------------------------
HEADER STATUS:-ENTERED        LINE STATUS:-ENTERED
HEADER STATUS:-BOOKED         LINE STATUS:-BOOKED
HEADER STATUS:-BOOKED         LINE STATUS:-AWAITING RECIEPT
HEADER STATUS:-BOOKED         LINE STATUS:-SHIPPED
HEADER STATUS:-BOOKED         LINE STATUS:-CLOSED
----------------------------------------------------------------

*****************BACK TO BACK ORDER***********************************

STEP1:-ENTER THE DETAILS THEN SAVE(IN SHIPPING TAB SOURCETYPE=EXTERNAL)
   
       HEADER STATUS:--ENTERED
       LINE STATUS  :--ENTERED

STEP2:-BOOK THE ORDER

       HEADER STATUS:--BOOKED
       LINE STATUS  :--SUPPLY ELIGIBLE
 
STEP3:- NOW:--->RIGHT CLICK ON SUPPLY ELIGIBLE->PROGESS ORDER->OK

       HEADER STATUS:BOOKED
       LINES STATUS :External Req Requested
       (THE SALES ORDER DETAILS MUST BE PRESENT IN REQUISITION INTERFACE TABLE)

STEP4:-NOW RUN THE REQUISITION IMPORT(PARAM:ITEMTYPE=CTO(CONFIGURE TO ORDER),ITEM,NO,YES)
       TO LOAD THE DATA INTO REQUISITION BASE TABLES
   
       HEADER STATUS:--BOOKED
       LINE STATUS  :--External Req Open
       (THE DETAILS MUST BE PRESENT IN REQUISITION BASE TABLE)

STEP5:-NOW COPY THE SALES ORDER NUMBER
       GOTO-->PURCHASING-->REQUISITION SUMMAY->RELATEC DOCUMENTS->SALES ORDER                      NUMBER->FIND->COPY REQUISITION NUMBER

       GOTO-->PURCHASE ORDERS->AUTO CREATE->DELETE SEARCH CRITIRIAA->ENTER REQUISITION             NUMBER->AUTOMETIC->ENTER SUPPLIER NAME->CREATE->PURCHASE ORDER WILL BE                      CREATED->COPY PO NUM&APPROVE THE PURCHASE ORDER

       HEADER STATUS:--BOOKED
       LINE STATUS  :--PO OPEN

STEP6:- GOTO-->RECIEVING->RECIEPTS->ENTER PONUM->FIND->SUB:STORES->SAVE

       HEADER STATUS:--BOOKED
       LINE STATUS  :--AWAITING SHIPPING
STEP7:-PICKING
STEP8:-SHIPPING
STEP9:-CLOSED
----------------------------------------------------------------
HEADER STATUS:-ENTERED        LINE STATUS:-ENTERED
HEADER STATUS:-BOOKED         LINE STATUS:-SUPPLY ELIGIBLE
HEADER STATUS:-BOOKED         LINE STATUS:-External Req Requested
HEADER STATUS:-BOOKED         LINE STATUS:-External Req Open
HEADER STATUS:-BOOKED         LINE STATUS:-PO OPEN
HEADER STATUS:-BOOKED         LINE STATUS:-AWAITING SHIPPING
HEADER STATUS:-BOOKED         LINE STATUS:-PICKED
HEADER STATUS:-BOOKED         LINE STATUS:-SHIPPED
HEADER STATUS:-BOOKED         LINE STATUS:-CLOSED
----------------------------------------------------------------

Relation between Oracle apps Modules with table column names

GL   AND   AP

GL_CODE_COMBINATIONS              AP_INVOICES_ALL

code_combination_id              =   acct_pay_code_combination_id

GL_CODE_COMBINATIONS              AP_INVOICES_DISTRIBUTIONS_ALL

code_combination_id              =             dist_code_combination_id

GL_SETS_OF_BOOKS                         AP_INVOICES_ALL

set_of_books_id                        =        set_of_books_id

GL   AND AR

GL_CODE_COMBINATIONS                        RA_CUST_TRX_LINE__GL_DIST_ALL

code_combination_id             = code_combination_id

GL   AND INV

GL_CODE_COMBINATIONS                        MTL_SYSTEM_ITEMS_B

code_combination_id             = cost_of_sales_account

GL   AND PO

GL_CODE_COMBINATIONS                        PO_DISTRIBUTIONS_ALL

code_combination_id             = code_combination_id

PO AND AP

PO_DISTRIBUTIONS_ALL                       AP_INVOICE_DISTRIBUTIONS_ALL

Po_distribution_id                  =                    po_distribution_id

PO_VENDORS                                            AP_INVOICES_ALL

vendor_id                                 =                     vendor_id

PO AND SHIPMENTS

PO_HEADERS_ALL                                           RCV_TRANSACTIONS

Po_header_id                         =                             po_header_id

PO_DISTRIBUTIONS_ALL                              RCV_TRANSACTIONS

Po_distribution_id                 =             po_distribution_id

SHIPMENTS AND INVOICE

RCV_TRANSACTIONS                AP_INVOICE_DISTRIBUTIONS_ALL

RCV_TRANSACTION_ID       =                            RCV_TRANSACTION_ID

PO AND  INV

PO_REQUISITION_LINES_ALL                  MTL_SYSTEM_ITEMS_B

item_id                                =           inventory_item_id

org_id                                   =           organization_id

PO AND HRMS

PO_HEADERS_ALL                                           HR_EMPLOYEES

Agent_id                             =             employee_id

PO AND REQUISITION

PO_DISTRIBUTIONS_ALL                                 PO_REQ_DISTRIBUTIONS_ALL

req_distribution_id                          =             distribution_id

SHIPMENTS AND INV

RCV_TRANSACTIONS                                     MTL_SYSTEM_ITEMS_B

Organization_id                                =             organization_id

INV AND HRMS

MTL_SYSTEM_ITEMS_B                                HR_EMPLOYEES

buyer_id                                              =             employee_id

OM  AND  AR

OE_ORDER_HEADERS_ALL                          RA_CUSTOMER_TRX_LINES_ALL

TO_CHAR( Order_number)                   =   interface_line_attribute1

OE_ORDER_LINES_ALL                                RA_CUSTOMER_TRX_LINES_ALL

TO_CHAR(Line_id)                                   =   interface_line_attribute6

OE_ORDER_LINES_ALL                          RA_CUSTOMER_TRX_LINES_ALL

reference_customer_trx_line_id =   customer_trx_line_id

OM AND SHIPPING

OE_ORDER_HEADERS_ALL        WSH_DELIVARY_DETAILS

HEADER_ID                         =   SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL        WSH_DELIVARY_DETAILS

LINE_ID                        =     SOURCE_LINE_ID

AP AND AR (BANKS)

AR_CASH_RECEIPTS_ALL AP_BANK_ACCOUNTS

REMITTANCE_BANK_ACCOUNT_ID = ABA.BANK_ACCOUNT_ID

AP AND AR

HZ_PARTIES                      AP_INVOICES_ALL

PARTY_ID                         =   PARTY_ID

OM AND CRM

OE_ORDER_LINES_ALL                 CSI_ITEM_INSTANCES(Install Base)

LINE_ID                           =   LAST_OE_ORDER_LINE_ID

Implementing MOAC In Oracle Apps R12

http://www.youtube.com/watch?v=i3xu818vMQM

OM Sales order query in oracle apps

SELECT
      H.ORDER_NUMBER,
      PARTY.PARTY_NAME CUSTOMER_NAME,
      OT.NAME ORDER_TYPE,
      CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER,
      H.CUST_PO_NUMBER,
      H.ORDERED_DATE,
      DECODE (SOLD_ARL.MEANING, NULL, NULL, ' ' || SOLD_ARL.MEANING)
                                                              SOLD_TO_CONTACT,
      PL.NAME PRICE_LIST,
      H.BLANKET_NUMBER,
      H.SALESREP_ID,  -----NO NAME AVAILABLE ONLY ID
      SHIP_SU.LOCATION SHIP_TO_LOCATION,
      SHIP_LOC.ADDRESS1 SHIP_TO_ADDRESS1,
      DECODE (SHIP_LOC.COUNTRY, NULL, NULL, SHIP_LOC.COUNTRY)
                                          SHIP_TO_ADDRESS5,
      BILL_SU.LOCATION INVOICE_TO_LOCATION,
      BILL_LOC.ADDRESS1 INVOICE_TO_ADDRESS1,
      BILL_LOC.ADDRESS2 INVOICE_TO_ADDRESS2,
      DECODE (BILL_LOC.COUNTRY, NULL, NULL, BILL_LOC.COUNTRY)
                                                          INVOICE_TO_ADDRESS5,
      H.FLOW_STATUS_CODE,
      H.TRANSACTIONAL_CURR_CODE,
      L.LINE_NUMBER,
      L.ORDERED_ITEM,
      L.ORDERED_QUANTITY,
      SHIP_FROM_ORG.ORGANIZATION_CODE SHIP_FROM,
      L.SOURCE_TYPE_CODE,
      L.SCHEDULE_SHIP_DATE,
      L.SCHEDULE_ARRIVAL_DATE,
      L.REQUEST_DATE,
      L.PROMISE_DATE,
      L.ORDERED_QUANTITY,
      L.SHIPPING_METHOD_CODE,
      L.FREIGHT_TERMS_CODE,
      PARTY.PARTY_NAME SOLD_TO
FROM
    OE_ORDER_HEADERS_ALL H,
    HZ_PARTIES PARTY,
    HZ_CUST_ACCOUNTS CUST_ACCT,
    OE_TRANSACTION_TYPES_TL OT,
    AR_LOOKUPS SOLD_ARL,
    HZ_PARTIES SOLD_PARTY,
    HZ_RELATIONSHIPS SOLD_REL,
    HZ_CUST_ACCOUNTS SOLD_ACCT,
    HZ_CUST_ACCOUNT_ROLES SOLD_ROLES,
    QP_LIST_HEADERS_TL PL,
    HZ_CUST_SITE_USES_ALL SHIP_SU,
    HZ_CUST_ACCT_SITES_ALL SHIP_CAS,
    HZ_LOCATIONS SHIP_LOC,
    HZ_PARTY_SITES SHIP_PS,
    HZ_CUST_SITE_USES_ALL BILL_SU,
    HZ_CUST_ACCT_SITES_ALL BILL_CAS,
    HZ_LOCATIONS BILL_LOC,
    HZ_PARTY_SITES BILL_PS,
    -----------------------
    OE_ORDER_LINES_ALL L,
    MTL_PARAMETERS SHIP_FROM_ORG,
    AR_LOOKUPS SHIP_ARL,
    ----------------------
    HZ_PARTIES SHIP_PARTY,
    HZ_CUST_ACCOUNTS SHIP_ACCT,
    HZ_RELATIONSHIPS SHIP_REL,
    HZ_CUST_ACCOUNT_ROLES SHIP_ROLES
WHERE
    H.ORDER_NUMBER=66404
    AND H.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID
    AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
    --------------------------------------------
    AND  H.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID
    --------------------------------------------
    AND H.SOLD_TO_CONTACT_ID = SOLD_ROLES.CUST_ACCOUNT_ROLE_ID(+)
    AND SOLD_ROLES.PARTY_ID = SOLD_REL.PARTY_ID(+)
    AND SOLD_ROLES.ROLE_TYPE(+) = 'CONTACT'
    AND SOLD_ROLES.CUST_ACCOUNT_ID = SOLD_ACCT.CUST_ACCOUNT_ID(+)
    AND NVL (SOLD_REL.OBJECT_ID, -1) = NVL (SOLD_ACCT.PARTY_ID, -1)
    AND SOLD_REL.SUBJECT_ID = SOLD_PARTY.PARTY_ID(+)
    AND SOLD_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
    AND SOLD_ARL.LOOKUP_CODE(+) = SOLD_PARTY.PERSON_PRE_NAME_ADJUNCT
    ------------------------------------------------------------------
    AND H.PRICE_LIST_ID = PL.LIST_HEADER_ID(+)
    AND PL.LANGUAGE(+) = USERENV ('LANG')
    ----------------------------------------------------------
    AND H.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID(+)
    AND SHIP_SU.CUST_ACCT_SITE_ID = SHIP_CAS.CUST_ACCT_SITE_ID(+)
    --------------------------------------------------------------
    AND H.SHIP_TO_ORG_ID = SHIP_SU.SITE_USE_ID
    AND SHIP_SU.CUST_ACCT_SITE_ID = SHIP_CAS.CUST_ACCT_SITE_ID
    AND SHIP_CAS.PARTY_SITE_ID = SHIP_PS.PARTY_SITE_ID
    AND SHIP_LOC.LOCATION_ID = SHIP_PS.LOCATION_ID
    -------------------------------------------------------
    AND H.INVOICE_TO_ORG_ID = BILL_SU.SITE_USE_ID(+)
    AND BILL_SU.CUST_ACCT_SITE_ID = BILL_CAS.CUST_ACCT_SITE_ID(+)
    --------------------------------------------------------------
    AND H.INVOICE_TO_ORG_ID = BILL_SU.SITE_USE_ID(+)
    AND BILL_SU.CUST_ACCT_SITE_ID = BILL_CAS.CUST_ACCT_SITE_ID(+)
    AND BILL_CAS.PARTY_SITE_ID = BILL_PS.PARTY_SITE_ID(+)
    AND BILL_LOC.LOCATION_ID(+) = BILL_PS.LOCATION_ID
    --------------------------------------------------
    -------------------------------------------------
    AND L.HEADER_ID = H.HEADER_ID
    -------------------------------------------------
    AND L.SOLD_TO_ORG_ID = CUST_ACCT.CUST_ACCOUNT_ID(+)
    AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
    AND L.SHIP_FROM_ORG_ID = SHIP_FROM_ORG.ORGANIZATION_ID(+)
    ---------------------------------------------------------
    AND L.SHIP_TO_CONTACT_ID = SHIP_ROLES.CUST_ACCOUNT_ROLE_ID(+)
    AND SHIP_ROLES.PARTY_ID = SHIP_REL.PARTY_ID(+)
    AND SHIP_ROLES.ROLE_TYPE(+) = 'CONTACT'
    AND SHIP_REL.SUBJECT_ID = SHIP_PARTY.PARTY_ID(+)
    AND SHIP_ROLES.CUST_ACCOUNT_ID = SHIP_ACCT.CUST_ACCOUNT_ID(+)
    AND NVL (SHIP_REL.OBJECT_ID, -1) = NVL (SHIP_ACCT.PARTY_ID, -1)
    AND SHIP_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
    AND SHIP_ARL.LOOKUP_CODE(+) = SHIP_PARTY.PERSON_PRE_NAME_ADJUNCT

items creation through API apporach in oracle apps

Items Creation throug API approach

--************package specifications*****************

CREATE OR REPLACE PACKAGE ITEMAPI_PKG IS
PROCEDURE MAP(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE VALIDATE(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE LOAD(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE SUBMIT_PROGRAM(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE ERROR_REPORT(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
PROCEDURE CALL_ALL_PRODS(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER);
END ITEMAPI_PKG;
/

--******************package body*********************

CREATE OR REPLACE PACKAGE BODY ITEMAPI_PKG IS

--****************map proc is started here**************

PROCEDURE MAP(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
BEGIN
UPDATE ITEMAPI SET API_VERSION='1.0';
COMMIT;
END MAP;

--***************map proc is ended here*******************

-----------------------------------------------------------------

--*************validate proc is started here***************

PROCEDURE VALIDATE(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
V_ORGANIZATION_ID NUMBER;
CURSOR ORG_CUR IS
SELECT * FROM ITEMAPI;
BEGIN
FOR ORG_REC IN ORG_CUR LOOP
BEGIN
SELECT ORGANIZATION_ID INTO V_ORGANIZATION_ID FROM ORG_ORGANIZATION_DEFINITIONS
WHERE UPPER(ORGANIZATION_CODE)=UPPER(ORG_REC.ORGANIZATION_CODE);
UPDATE ITEMAPI SET  STATUS_FLAG='V'WHERE TRANS_ID=ORG_REC.TRANS_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE ITEMAPI SET STATUS_FLAG='VE',ERROR_MSG='INVALID ORGANIZATION'
WHERE TRANS_ID=ORG_REC.TRANS_ID;
WHEN TOO_MANY_ROWS THEN
UPDATE ITEMAPI SET STATUS_FLAG='VE',ERROR_MSG='MORE THAN ONE ORGANIZATION EXITS'
WHERE TRANS_ID=ORG_REC.TRANS_ID;
WHEN OTHERS THEN
UPDATE ITEMAPI SET STATUS_FLAG='VE',ERROR_MSG='INVALID ORGANIZATION'
WHERE TRANS_ID=ORG_REC.TRANS_ID;
END;
END LOOP;
COMMIT;
END VALIDATE;

--**************validate proc is ended here****************

------------------------------------------------------------------

--**************load proc is started here*******************

PROCEDURE LOAD(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
CURSOR ITEM_CUR IS SELECT * FROM ITEMAPI;

      V_Inventory_Item_Id        NUMBER;
      V_Organization_Id          NUMBER;
      V_return_status            VARCHAR2(90);
      V_msg_count                NUMBER;
      V_msg_data                 VARCHAR2(90);

BEGIN
FOR ITEM_REC IN ITEM_CUR LOOP
BEGIN
    fnd_global.APPS_INITIALIZE(USER_ID=>1318,RESP_ID=>50583,RESP_APPL_ID=>401);
     ego_item_pub.PROCESS_ITEM(
      p_api_version  =>1.0,---------------------map
      p_init_msg_list =>  FND_API.G_TRUE,
      p_commit        => FND_API.G_TRUE,
      p_Transaction_Type =>ITEM_REC.TRANSACTION_TYPE,
      p_Segment1   =>ITEM_REC.SEGMENT1,
      p_Description =>ITEM_REC.DESCRIPTION,
      p_Long_Description =>ITEM_REC.LONG_DESCRIPTION,
      p_Organization_Id  =>ITEM_REC.ORGANIZATION_ID,
      p_Template_Id     =>ITEM_REC.TEMPLATE_ID,----------------------map
      p_Inventory_Item_Status_Code =>ITEM_REC.STATUS_CODE,
      p_approval_status            => ITEM_REC.APPROVAL_STATUS,
      x_Inventory_Item_Id =>V_Inventory_Item_Id,
      x_Organization_Id    =>V_Organization_Id,
      x_return_status      => V_return_status,
      x_msg_count          => V_msg_count,
      x_msg_data          => V_msg_data);
      dbms_output.put_line(V_Inventory_Item_Id);
      dbms_output.put_line(V_Organization_Id);
           dbms_output.put_line(V_return_status);
                dbms_output.put_line(V_msg_count);
                     dbms_output.put_line(V_msg_data);
COMMIT;
END;
END LOOP;
END LOAD;

--**************load proc is ended here**************

----------------------------------------------------------------------

--**************no submit program in API approach bcz there is no standard program API is the responsible of loading the data into base tables(No interface tables)************

PROCEDURE SUBMIT_PROGRAM(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
BEGIN
NULL;
END SUBMIT_PROGRAM;

--********************error_report proc started here ******************

PROCEDURE ERROR_REPORT(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
V NUMBER;
VE NUMBER;
LE NUMBER;
MM NUMBER;
CURSOR ERR_CUR IS SELECT * FROM ITEMAPI WHERE STATUS_FLAG NOT IN('V');
BEGIN
SELECT COUNT(*) INTO MM FROM ITEMAPI;
SELECT COUNT(*) INTO V FROM ITEMAPI WHERE STATUS_FLAG='V';
SELECT COUNT(*) INTO VE FROM ITEMAPI WHERE STATUS_FLAG='VE';
SELECT COUNT(*) INTO LE FROM ITEMAPI WHERE STATUS_FLAG='LE';
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'                                                     ITEM CREATION ERROR REPORT');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'                                                     ---------------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'SUMMARY DETAILS:');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'_______________ ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS PROCESSED',36,' ')||':'||MM);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS SUCCESFULLY VALIDATED',36,' ')||':'||V);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS FAILED IN VALIDATION',36,' ')||':'||VE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('NO OF RECODRS FAILED IN LOAD',36,' ')||':'||LE);
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'ERROR RECORDS:');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('TRANS_ID',20,' ')|| RPAD('SEGMENT1',30,' ')||
RPAD('ORGANIZATION_CODE',30,' ')||RPAD('ERROR_MESSAGE',500,' '));
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD('______________',20,' ')|| RPAD('_________',30,' ')||
RPAD('________',30,' ')||RPAD('_____________',500,' '));

FOR ERR_REC IN ERR_CUR LOOP
BEGIN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,RPAD(ERR_REC.TRANS_ID,30,' ')|| RPAD(ERR_REC.SEGMENT1,30,' ')||
RPAD(ERR_REC.ORGANIZATION_CODE,20,' ')||RPAD(    ERR_REC.ERROR_MSG,500,'    '));

END;
END LOOP;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'                                            *********END OF THE ERROR REPORT**********');


END ERROR_REPORT;

--****************error_report proc is ended here******************

------------------------------------------------------------------------

--****************Call all procds proc is started here*************

PROCEDURE CALL_ALL_PRODS(ERRBUF OUT VARCHAR2,RETCODE OUT NUMBER)IS
v1 varchar2(90);
v2 number;
BEGIN
map(v1,v2);
validate(v1,v2);
load(v1,v2);
error_report(v1,v2);
END CALL_ALL_PRODS;

--**********call all prods proc is ended here*************************
END ITEMAPI_PKG;
/