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;
/

Island Of Oracle Apps: PRAGMA AUTONOMOUS_TRANSACTION IN ORACLE

Island Of Oracle Apps: PRAGMA AUTONOMOUS_TRANSACTION IN ORACLE

PRAGMA AUTONOMOUS_TRANSACTION IN ORACLE

Island Of Oracle Apps: SQL Functions

Island Of Oracle Apps: SQL Functions

SQL Functions

oracle reports interview questions and answers

How many different triggers are available in Report?

 There are five types of triggers in report 6i

 1) Before report trigger

 2) After report trigger

 3) Before Parameter trigger

 4) After parameter trigger

 5) Between pages trigger

What is the Firing sequence of report trigger?

 First the before parameter trigger will raise, after firing this trigger parameter form will displayed,  after passing parameter after parameter trigger will fire query will parsed &  then before report trigger will fired then if there are number of pages in your report  then the between pages trigger will fired but it will fire between first & second & so on pages but it will not fired in reverse condition the after report trigger will fire  after closing the run time parameter form is closed.

What is the difference between After Parameter Trigger and Before Report Trigger?

 After parameter Trigger: It will fire after the parameter form is displayed.here we can do validation on parameter values Before Report Trigger: It will fire before the report is executed and after the query is parsed and date is fetched.

What is the Format Trigger?

 Format Trigger is a PL/SQL function. This trigger is going to fire before an object is printed in report output. it return boolean-true then go to print -false then don't print.

What is the diff. when Flex mode is mode on and when it is off?

 When flex mode is on, reports automatically resizes the parent when the child is resized.

What is the diff. when confine mode is on and when it is off?

 When confine mode is on, an object cannot be moved outside its parent in the layout.

What is a lexical parameter?

 Lexical Parameter is used to replace the where, order by conditions at run time.

What are bind variables?

 Bind variables are used in report 6i for replacing the single parameter in the select statement  

How many different layouts are available in Reports?

 There are eight different layout formats:

 1. Tabular

 2. Form Like

 3. Form Letter

 4. Mailing Label

 5. Group Left

 6. Group Above

 7. Matrix

 8. Matrix with group

What is the minimum number of groups required for a matrix report?

 The minimum of groups required for a matrix report are 4

What is the lock option in reports layout?

 By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.

What is the Anchoring in Reports?

 Anchor is used to make fixed distance between two objects in Reports Layout.  

What is the difference between Frame and Repeating Frame?

 Frames are used to surround other objects and protect them from being overwritten or pushed by other objects. For example a frame might be used to surround all objects owned by a group to surround column headings or to surround summaries.  When you default the layout for a report Report Builder creates frames around report objects as needed; you can also create a frame manually in the Layout Model view.Repeating frames surround all of the fields that are created for a group’s columns. The repeating frame prints (is fired) once for each record of the group.When you default the layout for a report Report Builder creates repeating frames around fields as needed; you can also create a repeating frame manually in the Layout Model view

What are different types of column in reports?

 There are three types of columns in the report 6i these are:

 1) Placeholder Column – Placeholder column is used to store a value for a variable.

 2) Formula Column

 3) Summary Column

How many types of columns are there and what are they?

 Formula columns: For doing mathematical calculations and returning one value

 Summary Columns: For doing summary calculations such as summations etc.

 Place holder Columns: These columns are useful for storing the value in a variable

Can u have more than one layout in report?

 It is possible to have more than one layout in a report by using the additional layout option in the layout editor. Yes it is possible to run the report without parameter form by setting the PARAM value to Null

REPORTS

1.    What are the steps involved in Reports Customization?

1.    Copy the source file into custom directory and rename if required.

2.    Carry out the required changes.

3.    Create a new executable and concurrent program.

4.    Attach to a Request Group and subsequently to a Responsibility.

2.    How do you implement customized reports into Oracle Apps?

Create a new executable and concurrent program.

Attach to a Request Group and subsequently to a Responsibility.

3.    What is a User Exit?

User exit is a program, which enables executing external procedures.

4.    What are the user exits available in Oracle Apps?

FND SRWINIT, FND SRWEXIT, FND FORMAT_CURRENCY,

FND FLEXIDVAL, FND FLEXSQL.

5.    What is the use of FND SRWINIT?

It is used to set Profile values and environmental variables.

6.    What is the use of FND SRWEXIT?

It is used to release the memory used by Profile and Environmental variables called by SRWINIT.

7.    How do you find the report source file name?

Report’s source file name can be found from the log file of that report.

8.    Where do you find a report’s source file?

It will be found in the corresponding report directory of the Product Top.

9.    In which directory do you store the customized reports?

Report directory of Custom Top.

10. How do you ensure that customized reports are not overwritten during an upgrade?

By placing them under Custom directory.

11. Why is FND FORMAT_CURRENCY used exit called?

This user exit is used for formatting currencies dynamically.

12. How do you pass parameters to a report?

Parameters can be passed to a report via Tokens from a Concurrent Program.

13. What is the use of $FLEX$?

$FLEX$ is used in value sets to retrieve values based on a value of another value set.

14. What is FND FLEXIDVAL used for?

This user exit is used to populate fields for display. You pass the key flex fields data retrieved by the query into this user exit from the formula column. With is user exit, you can display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

15. What are the different methods of doing a Flex field report?

There are two methods of doing a Flex Field report.

1.    By using Flex Field views.

2.    By using Flex Field user exits.

16. Is there any template for reports customization?

No.

17. From which trigger do you call the SRWINIT user exit?

Before Report Trigger.

18. From which trigger do you call the SRWEXIT user exit?

After Report Trigger.

19. Where are the log files and output files of a report stored?

Log files and Out files are stored under the Log and Out directories

Of the respective Product directory.

20. When do you call SRW.Reference?

SRW.Reference is called when you want to access the current value of any

Item / Variable in a report.

21. What is the purpose of calling FND FLEXSQL?

This user exit is called to create a SQL fragment usable by your report to

tailor your SELECT statement that retrieves flex field values. This

fragment allows you to SELECT flex field values or to create a WHERE,

ORDER BY, GROUP BY, or HAVING clause to limit or sort the flex field values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement.

22. How do restrict a user from accessing a report?

By assigning responsibilities.

23. How do you access profile values from a report?

By using appropriate procedures from FND_PROFILE.

24. What are the required parameters if you want to create a flex field report ?

·         P_CONC_REQUEST_ID.

The user exit FND SRWINIT uses this parameter to retrieve information about the concurrent request that started this report.

           

·         P_FLEXDATA