, , ,

What is SQL* Loader?

What is SQL*Loader?
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.

Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

What is Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

What is Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl

Control File Creation:

Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS
(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)
This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method

INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.

APPEND: This mode will be use to attach the new record to the existing records.

REPLACE: This will replace the existing records with new records.
C:> sqlldr userid/passward@Database control=text1.ctl path=direct

SQL* Loader Paths: We can execution SQL* loader in two paths or nodes
Direct
Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant

/by
, , , , , ,

Payables Open Interface for Project based Invoices

Following scripts used to test Payable open Interface for Invoices contains Project Data:

 Query for to get PO details:
 –select * from po_headers_all  where  PO_HEADER_ID=63845
–AND segment1=’NEX060378-002′
 –select * from po_lines_all where po_header_id=63845
–select * from po_vendors where vendor_id=11813
 –select * from po_line_locations_all where po_header_id=63845
  –select *  from PO_DISTRIBUTIONS_ALL
 –select * from ap_invoice_lines_interface
 –commit
Query to Set Org_id: 
–begin
–dbms_application_info.set_client_info(‘169’);
–end;

–select * from ap_invoices_interface where invoice_num like ‘COL%’
–select * from ap_invoice_lines_interface
— select * from  AP_TERMS
–select * from PO_DISTRIBUTIONS_ALL

DECLARE
    p_invoice_id       NUMBER;
    i                  NUMBER;
BEGIN

Query to Generate invoice_id —

    select AP_INVOICES_INTERFACE_S.nextval
    into p_invoice_id
    from dual;

Query to  Insert an invoice header —
 insert into ap_invoices_interface(INVOICE_ID,
                 INVOICE_NUM,
                   INVOICE_TYPE_LOOKUP_CODE,
                  INVOICE_DATE,
                 PO_NUMBER,
                 VENDOR_ID,
                 VENDOR_SITE_ID,
                   INVOICE_AMOUNT,
                 INVOICE_CURRENCY_CODE,
                  –TERMS_ID,
                   SOURCE,
                   GROUP_ID,
                PAYMENT_METHOD_LOOKUP_CODE,
                ORG_ID)
        VALUES  (p_invoice_id,         –INVOICE_ID,
                  ‘COL012’,              –INVOICE_NUM,
                  ‘STANDARD’,            –INVOICE_TYPE_LOOKUP_CODE,
                  sysdate,              –INVOICE_DATE,
                 ‘NEX060378-002’,                    –v_PO_NUMBER,
                11934,                 –v_VENDOR_ID, 11790
                14667,                 –v_VENDOR_SITE_ID, 14506
                10,                       –v_INVOICE_AMOUNT,  (Sanjiv Wrong Amt)10
               ‘USD’,                  — v_INVOICE_CURRENCY_CODE, usd
                –10011,                  –v_TERMS_ID, (Sanjiv)
                ‘CONVERSION’,–‘INVOICE GATEWAY’,       –v_SOURCE,
                 ‘USM’,                  — v_GROUP_ID,
               ”,                     — v_PAYMENT_METHOD_LOOKUP_CODE, CHECK
               169);                   –v_ORG_ID
    — Insert invoice line —

    for i in 1..1 loop
INSERT into ap_invoice_lines_interface (INVOICE_ID,
                                INVOICE_LINE_ID,
                                LINE_NUMBER,
                                LINE_TYPE_LOOKUP_CODE,
                                AMOUNT,
                                ACCOUNTING_DATE,
                                FINAL_MATCH_FLAG,
                                PO_HEADER_ID,
                                PO_NUMBER,
                                 PO_LINE_ID,
                                 PO_LINE_NUMBER,
                                PO_LINE_LOCATION_ID,
                                PO_SHIPMENT_NUM,
                                INVENTORY_ITEM_ID,
                                ITEM_DESCRIPTION,
                                QUANTITY_INVOICED,
                                SHIP_TO_LOCATION_CODE,
                                UNIT_PRICE,
                                PO_RELEASE_ID,
                                RELEASE_NUM,
                                PRICE_CORRECTION_FLAG,
                                RECEIPT_NUMBER,
                                MATCH_OPTION,
                                RCV_TRANSACTION_ID,
                                DIST_CODE_COMBINATION_ID,
                                PO_DISTRIBUTION_ID,
                                PO_DISTRIBUTION_NUM,
                                PROJECT_ID,
                                TASK_ID,
                                EXPENDITURE_TYPE,
                                EXPENDITURE_ORGANIZATION_ID,
                                PA_QUANTITY,
                                EXPENDITURE_ITEM_DATE
                                )
                        VALUES (
                                 p_invoice_id,     –v_INVOICE_ID,
                                 AP_INVOICE_LINES_INTERFACE_S.nextval,       –v_INVOICE_LINE_ID,
                                     i,               –v_LINE_NUMBER,  1
                                  ‘ITEM’,           — v_LINE_TYPE_LOOKUP_CODE,
                                   10,            –v_AMOUNT, (Sanjiv Wrong Amt, PO Line=82.27) 10
                                  sysdate,           — v_ACCOUNTING_DATE,
                                  ”,                –v_FINAL_MATCH_FLAG,
                                  63845,                –v_PO_HEADER_ID,
                                 ‘NEX060378-002’,             — v_PO_NUMBER,
                                 63846,              –v_PO_LINE_ID, (Sanjiv Wrong POLineID) 47245
                                  1,               –v_PO_LINE_NUMBER,
                                  63846,           –v_PO_LINE_LOCATION_ID, (Sanjiv Wrong POLineLocID)
                                    ”,                –v_PO_SHIPMENT_NUM,
                                  ”,                –v_INVENTORY_ITEM_ID,
                                  ”,                –v_ITEM_DESCRIPTION,
                                  1,                –v_QUANTITY_INVOICED,
                                  ”,                –v_SHIP_TO_LOCATION_CODE,
                                 ”,                 –v_UNIT_PRICE,
                                ”,                 –v_PO_RELEASE_ID,
                                ”,                 –v_RELEASE_NUM,
                                   ”,                 –v_PRICE_CORRECTION_FLAG,
                                ”,                 –v_RECEIPT_NUMBER,
                                   ”,                –v_MATCH_OPTION,
                                   ”,                 –v_RCV_TRANSACTION_ID
                                ”,           –v_DIST_CODE_COMBINATION_ID
                                64302,            –PO_DISTRIBUTION_ID = 64302
                                1,                  –PO_DISTRIBUTION_NUM
                                ”,–1087,               –PROJECT_ID, (Sanjiv Wrong ProjID) 1172 1087
                                ”,–1551,                –TASK_ID,
                                ”,–‘Awrd Ovation Point Redemption’,–EXPENDITURE_TYPE,
                                 ”,–169,                –EXPENDITURE_ORGANIZATION_ID,
                                 ”,–”,             –PA_QUANTITY, (Sanjiv Wrong Qty)10
                              ” );– sysdate );           –EXPENDITURE_ITEM_DATE
      end loop;

 commit;
END;

Queries to debugged and trace Interface errors: 

–select * from ap_invoices_interface where invoice_id=13634
–select * from ap_invoice_lines_interface where invoice_id=13634
–select * from user_errors where name like’USM%’;
— select * from pa_projects_all where project_id=1172 –1087
–select * from pa_projects_all where project_id in (select project_id from pa_tasks where task_number=’2.4′)
–10437
–select * from usm_po_codes
–select * from pa_projects_all where segment1 =’10437′

/by
, , ,

Some Standrad Oracle Apps Report Customizations

1. Report Name :- Inactive Item Report         (Inventory)
Customization :-   Add one field Transaction Qty to report
Short Name :-  INVIRSLO
Parameter :- Organization,Subinv Break Option,Category Set Name,Inactive From Date
Table :-  mtl_system_items ,
               mtl_material_transactions ,
                mtl_item_categories,
                mtl_categories.
 
Solution  :-    See the parameter listing which is required for the report with the help of report name and short name .
                           Then  FPT the report from apps/viappl/inv/11.5.0/reports/US to your directory.
                        Go to the data module and click on the Query In the select statement add transaction Qty from MTL_MATERIAL_TRANSACTION
                 Go the report builder navigator window ->click on the icon Layout editor.
                Go to the header section separate all the frames of header section. 
place the boilerplate text for the status.Add One  field and give the source to the field as status
                 save the report -ftp the rdf file
                 create concurrent program with required parameter as per original report  attach that program to the Oracle Payble or your responsibility group
 This Customize Inactive Items Report Shows Added Customer Status.
******************************************************************************************************************************************************************************************
2. Report Name: – A02 Item Summary Listing Report (PO)
Customization: – Displays the summarized item listing with necessary information of buyer.
                          Added a group for category wise summation of list price.
Short Name: – POXSUMIT,     A02_POXSUMIT
Parameter: – Title, Active/inactive (Active, Inactive, Both)
Table: – GL_SETS_OF_BOOKS, FINANCIALS_SYSTEM_PARAMETERS (View),
            MTL_DEFAULT_SETS_VIEW (View), FND_LOOKUPS (View), GL_CODE_COMBINATIONS,
            MTL_UNITS_OF_MEASURE_VL (View), PO_AGENTS, PER_ALL_PEOPLE_F, MTL_SYSTEM_ITEMS,
            MTL_CATEGORIES (View), MTL_ITEM_CATEGORIES, PO_LOOKUP_CODES (View)
******************************************************************************************************************************************************************************************
3.Report Name: – Subinventory Quantity report:  (INV)
Customization: – Customized this report to displays  price of every item and their sub inventories with total of price.
Short Name: – INVIRSIQ, A02_INVIRSIQ
Parameter: –
Table: – MTL_SYSTEM_ITEMS            msi,  
   MTL_ONHAND_QUANTITIES_detail moqd,
   MTL_SECONDARY_INVENTORIES si,
   MTL_ITEM_LOCATIONS mil,
  CST_ITEM_COST_DETAILS CICD
  org_organization_definitions  ood
*********************************************************************************************************************************************************************************************
4. Internal Requisition  Status Report. (PO)
Customization :-   Add total for Quantity Ordered
Short Name :-POXRQSIN
Parameter :-
Table :-  po_requisition_lines ,  po_requisition_headers  ,  mtl_system_items ,  hr_employees  
 
Solution          :-      See the parameter listing which is required for the report with the help of report name and short name . Then  FPT the report from apps/viappl/inv/11.5.0/reports/US to your directory.
 Create one summary Column for the Quantity order for Sum  Go the report builder navigator window ->click on the icon layout add the total field  give the source of the summary column to that field  save the report -ftp the rdf file create concurrent program with required parameter as per original report attach that program to the Oracle Inventory or your responsibility group. This customize Internal Requisition status report shows total for quantity order.
/by
, , , , , ,

What is Subinventory Quantities report?

Form: INVIRSIQ
Use the Subinventory Quantities Report to show inventory item quantities by subinventory.
Report Submission
Use the On-hand Quantity Reports or Submit Requests window and enter Subinventory quantities report in the Name field to submit the report.
Report Parameters
Item Range
Choose one of the following options:
Full listing   :    Report all subinventories.
Partial list by :  Report only those subinventories for a inventory item  partialrange of items.
Specific          :  Report only the subinventory you subinventory  specify.
Items From/To
Enter a beginning and ending item to restrict the report to a range of items. You can enter an item here only if you enter Partial list by inventory item in the Item Range field.
Subinventory
Enter a subinventory for which to report on-hand quantity. You can enter a value here only if you enter Specific subinventory in the Item
Range field.
The original report has the following columns:
1.Item
2.Description
3.Rev
4.Locator
5.UOM
6.Quantity
Customization:
–Add the price of every item.
–Calculate the price*qty=total
–Add the subinventory total
This would give us the value of the sub inventory.
Tables Used:
1.MTL_SYSTEM_ITEMS (msi)
2.MTL_ONHAND_QUANTITIES_DETAIL (moqd)
3.MTL_SECONDARY_INVENTORIES (si)
4.MTL_ITEM_LOCATIONS (mil)
The tables added:
1.CST_ITEMS_COST (cs)
MTL_SYSTEM_ITEMS:
MTL_SYSTEM_ITEMS_B is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. You can specify item-related information in fields such as: Bill of Material, Costing, Purchasing, Receiving, Inventory,
Physical attributes, General Planning, MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing. You can set up the item with multiple segments, since it is implemented as a flexfield.
Use the standard ’System Items’ flexfield that is shipped with the product to configure your item flexfield. The flexfield code is MSTK. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Each item is initially defined in an item master organization. The user then assigns the item to other organizations that need to recognize this item; a row is inserted for each new organization the item is assigned to.
Many columns such as MTL_TRANSACTIONS_ENABLED_ FLAG and BOM_ENABLED_FLAG correspond to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are available to the user depend on which Oracle applications are installed. The table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item attributes and Oracle applications. Two unit of measure columns are stored in MTL_SYSTEM_ITEMS table.
PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character unit that is used throughout Oracle Purchasing. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL.
Translations table (MTL_SYSTEM_ITEMS_TL) holds item descriptions in multiple languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for backward compatibility and is maintained in the installation base language only.
MTL_ONHAND_QUANTITIES_DETAIL
MTL_ONHAND_QUANTITIES stores quantity on hand information
by control level and location.
MTL_ONHAND_QUANTITIES is maintained as a stack of receipt
records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria. Note that any transactions which are
committed to the table MTL_MATERIAL_TRANSACTIONS_TEMP are
considered to be played out as far as quantity on hand is concerned in Inventory transaction forms. All our Inquiry forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID and UPDATE_TRANSACTION_IDs to join to MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the
transactions that created the row and the transaction that last updated a row.
MTL_SECONDARY_INVENTORIES
MTL_SECONDARY_INVENTORIES is the definition table for the
subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this
item will physically exist in inventory.
Other attributes include general ledger accounts, demand picking order, locator type, availability type, and reservable type. You can also specify whether this subinventory is an expense or asset subinventory (ASSET_INVENTORY), or whether quantities are tracked
(QUANTITY_TRACKED).
MTL_ITEM_LOCATIONS
MTL_ITEM_LOCATIONS is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
The locator is a key flexfield. The Flexfield Code is MTLL.
CST_ITEMS_COST
CST_ITEM_COSTS stores item cost control information by cost type.
For standard costing organizations, the item cost control information for the Frozen cost type is created when you enter a new item. For average cost organizations, item cost control information is created when you transact the item for the first time.
You can use the Item Costs window to enter cost control information.
Where clause of the original report:
1.si.secondary_inventory_name = moqd.subinventory_code(+)
2.si.organization_id = moqd.organization_id (+)
3.moqd.organization_id = msi.organization_id(+)
4.moqd.inventory_item_id = msi.inventory_item_id (+)
5.moqd.locator_id = mil.inventory_location_id(+)
6.moqd.organization_id = mil.organization_id(+)
Additional where clause added for the customized report:
1.cs.inventory_item_id = msi.inventory_item_id
2.cs.organization_id = msi.organization_id
/by
, , , ,

How to Launch Workflow?

Following APIs used to Launch Workflow

declare
cursor c1 is
select incident_no,CREATED_BY_USER_ID from org_sr_summary a
where trunc(create_dt) = trunc(sysdate) and incident_no = ‘1203950’
and not exists ( select 1 from org_incident_history b where a.incident_no = b.incident_id);

v_return_status varchar2(150);
v_msg_count number;
v_msg_data varchar2(150);
v_itemkey varchar2(150);
v_initiator_user_id number;
v_count number := 0;
v_null number:=0;

begin

For i in c1
Loop

begin
select workflow_process_id into v_null
from cs_incidents_all_b
where incident_number = TO_CHAR(i.incident_no)
and workflow_process_id is null ;
exception
when no_data_found then dbms_output.put_line(‘Exception’);
end;

if v_null is null then

CS_WORKFLOW_PUB.LAUNCH_SERVEREQ_WORKFLOW(p_api_version => 1
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_return_status => v_return_status
,p_msg_count => v_msg_count
,p_msg_data => v_msg_data
,p_request_number => i.incident_no
,p_initiator_user_id => i.CREATED_BY_USER_ID
,p_initiator_resp_id => NULL
,p_initiator_resp_appl_id => NULL
,p_itemkey => v_itemkey
, p_nowait => FND_API.G_FALSE);

v_count := v_count + 1;
end if;

END LOOP;
dbms_output.put_line(‘total count = ‘||v_count);
commit;
end;

SELECT b.process_name, b.instance_label, a.activity_result_code,
a.activity_status, a.item_key
FROM wf_item_activity_statuses a, wf_process_activities b
WHERE a.process_activity = b.instance_id
AND a.item_type = ‘SERVEREQ’
AND a.item_key LIKE ‘1203968%’
AND a.activity_status = ‘DEFERRED’

begin
wf_engine.handleerror(itemtype => ‘SERVEREQ’,
itemkey => ‘1203968-757581’,
activity => ‘XX_WF:STANDARDWAIT-1’ ,
command => ‘RETRY’);
commit;
end;

declare
 cursor c1 is
 select incident_no,CREATED_BY_USER_ID from org_sr_summary a
 where trunc(create_dt) = trunc(sysdate) and incident_no = 1199236;
 –and not exists ( select 1 from org_incident_history b where a.incident_no = b.incident_id);
  v_return_status        varchar2(150);
  v_msg_count            number;
  v_msg_data            varchar2(150);
  v_itemkey                varchar2(150);
  v_initiator_user_id   number;
  v_count                number := 0;
  v_null                number:=0;
begin
     For i in c1
     Loop
      begin
      select workflow_process_id into v_null
      from      cs_incidents_all_b
      where  incident_number = TO_CHAR(i.incident_no)
      and  workflow_process_id is null ;
      exception
      when no_data_found then null;
      end;
      if v_null is null then
      CS_WORKFLOW_PUB.LAUNCH_SERVEREQ_WORKFLOW(p_api_version                    => 1
                                             ,p_init_msg_list                 => FND_API.G_FALSE
                                             ,p_commit                       => FND_API.G_FALSE
                                             ,p_return_status               => v_return_status
                                             ,p_msg_count                   => v_msg_count  
                                             ,p_msg_data                       => v_msg_data
                                             ,p_request_number               => i.incident_no
                                             ,p_initiator_user_id              => i.CREATED_BY_USER_ID
                                             ,p_initiator_resp_id              => NULL
                                             ,p_initiator_resp_appl_id        => NULL
                                             ,p_itemkey                       => v_itemkey
                                             , p_nowait                           => FND_API.G_FALSE);

    v_count := v_count + 1;
     IF v_msg_count > 0 THEN

        FOR I IN 1..v_msg_count LOOP

        dbms_output.put_line(‘I.’|| SUBSTR (FND_MSG_PUB.Get(p_encoded =>FND_API.G_FALSE ), 1, 255));

        END LOOP;

    END IF;
    end if;
   END LOOP;  
    dbms_output.put_line(‘total count = ‘||v_count);
    dbms_output.put_line(‘return status = ‘||v_return_status);
        dbms_output.put_line(‘ v_msg_data = ‘|| v_msg_data);
    dbms_output.put_line(‘  v_itemkey = ‘|| v_itemkey);
commit;
end;

/by