Alternative Way of Deleting an Incomplete Purchase Order

Alternative Way of Deleting an Incomplete Purchase Order
************************************************************
Before approving the Purchase Order & when the PO is in INCOMPLETE status, Oracle allows us to delete the same from the Purchase Order Form. The same can be achieved using the following mentioned API.
But we need to remember few points before using this API for deleting Incomplete PO.
- The PO Should be INCOMPLETE & Should not be Approved Once.
- Once the PO is approved, records gets inserted into mtl_supply & all the archive tables.
This API never deletes the records from the mtl_supply & archive tables and only deletes the
records from the base tables.
-- Sample Script to Delete Incomplete PO Using 'PO_HEADERS_SV1'
--=================================================
DECLARE

l_deleted BOOLEAN;

CURSOR c_po_det IS

SELECT po_header_id, segment1,org_id
FROM po.po_headers_all pha
WHERE 1 = 1
AND segment1 = '11170002356'
AND org_id = 308
AND NVL (approved_flag, 'N') <> 'Y'
AND NVL (cancel_flag, 'N') = 'N'
AND type_lookup_code = 'STANDARD'
AND authorization_status = 'INCOMPLETE'
AND NOT EXISTS ( SELECT 1
FROM po_headers_archive_all a
WHERE pha.po_header_id = a.po_header_id
AND pha.org_id = a.org_id)
AND NOT EXISTS ( SELECT 1
FROM mtl_supply b WHERE pha.po_header_id = b.po_header_id AND
supply_type_code = 'PO');

BEGIN

FOR c1 IN c_po_det LOOP

DBMS_OUTPUT.put_line ( 'Calling PO_HEADERS_SV1 API To Delete PO');
DBMS_OUTPUT.put_line ( '==========================');

l_deleted := po_headers_sv1.delete_po (c1.po_header_id, 'STANDARD');

IF l_deleted = TRUE
THEN
COMMIT;
DBMS_OUTPUT.put_line ( 'Successfully Deleted the PO');
ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ( 'Failed to Delete the PO');
END IF;
END LOOP;

END;

Sample Procedure for removing end date from Responsibilities given to Users

Sometimes while working on a support projects, we used to have access to the read only responsibilities ,or though we have given the access to the super users initially, but those accesses might have revoked after system went live. But in test environment, we may require those accesses back so as to fix the bugs or to test the functionality.
The removing of end date from a responsibility which is already assigned to a user, can be done using fnd_user_resp_groups_api API.
-------------------------------------------------------------------------------------------------------------------------
DECLARE

p_user_name VARCHAR2 (50) := 'A42485';
p_resp_name VARCHAR2 (50) := 'Order Management Super User';
v_user_id NUMBER (10) := 0;
v_responsibility_id NUMBER (10) := 0;
v_application_id NUMBER (10) := 0;

BEGIN

BEGIN
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE UPPER (user_name) = UPPER (p_user_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('User not found');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding User.');
RAISE;
END;

BEGIN
SELECT application_id, responsibility_id
INTO v_application_id, v_responsibility_id
FROM fnd_responsibility_vl
WHERE UPPER (responsibility_name) = UPPER (p_resp_name);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Responsibility not found.');
RAISE;
WHEN TOO_MANY_ROWS
THEN
DBMS_OUTPUT.put_line
('More than one responsibility found with this name.');
RAISE;
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error finding responsibility.');
RAISE;
END;

BEGIN

DBMS_OUTPUT.put_line ('Initializing The Application');

fnd_global.apps_initialize (user_id => v_user_id,
resp_id => v_responsibility_id,
resp_appl_id => v_application_id
);

DBMS_OUTPUT.put_line
('Calling FND_USER_RESP_GROUPS_API API To Insert/Update Resp');

fnd_user_resp_groups_api.update_assignment
(user_id => v_user_id,
responsibility_id => v_responsibility_id,
responsibility_application_id => v_application_id,
security_group_id => 0,
start_date => SYSDATE,
end_date => NULL,
description => NULL
);

DBMS_OUTPUT.put_line
('The End Date has been removed from responsibility');
COMMIT;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error calling the API');
RAISE;
END;
END;

R12 - FND - Script to add responsibility using fnd_user_pkg with validation

Some times we don't have the access to add the responsibility to the user using the the Create User form. So for this Oracle is having one API fnd_user_pkg.addresp which can do the job without using the Create User Form.

R12 - FND - Script to add responsibility using fnd_user_pkg with validation


DECLARE

v_user_name VARCHAR2 (10) := '&Enter_User_Name';
v_resp_name VARCHAR2 (50) := '&Enter_Existing_Responsibility_Name';
v_req_resp_name VARCHAR2 (50) := '&Enter_required_Responsibility_Name';
v_user_id NUMBER (10);
v_resp_id NUMBER (10);
v_appl_id NUMBER (10);
v_count NUMBER (10);
v_resp_app VARCHAR2 (50);
v_resp_key VARCHAR2 (50);
v_description VARCHAR2 (100);
RESULT BOOLEAN;

BEGIN

SELECT fu.user_id, frt.responsibility_id, frt.application_id
INTO v_user_id, v_resp_id, v_appl_id
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = 'US'
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_resp_name;
fnd_global.apps_initialize (v_user_id, v_resp_id, v_appl_id);

SELECT COUNT (*)
INTO v_count
FROM fnd_user fu,
fnd_responsibility_tl frt,
fnd_user_resp_groups_direct furgd
WHERE fu.user_id = furgd.user_id
AND frt.responsibility_id = furgd.responsibility_id
AND frt.LANGUAGE = 'US'
AND fu.user_name = v_user_name
AND frt.responsibility_name = v_req_resp_name;

IF v_count = 0 THEN

SELECT fa.application_short_name, frv.responsibility_key,
frv.description
INTO v_resp_app, v_resp_key,
v_description
FROM fnd_responsibility_vl frv, fnd_application fa
WHERE frv.application_id = fa.application_id
AND frv.responsibility_name = v_req_resp_name;

fnd_user_pkg.addresp (
username => v_user_name,
resp_app => v_resp_app,
resp_key => v_resp_key,
security_group => 'STANDARD',
description => v_description,
start_date => SYSDATE - 1,
end_date => NULL);

RESULT :=
fnd_profile.SAVE (x_name => 'APPS_SSO_LOCAL_LOGIN',
x_value => 'BOTH',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'FND_CUSTOM_OA_DEFINTION',
x_value => 'Y',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'FND_DIAGNOSTICS',
x_value => 'Y',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'DIAGNOSTICS',
x_value => 'Y',
x_level_name => 'USER',
x_level_value => v_user_id
);

RESULT :=
fnd_profile.SAVE (x_name => 'FND_HIDE_DIAGNOSTICS',
x_value => 'N',
x_level_name => 'USER',
x_level_value => v_user_id
);

DBMS_OUTPUT.put_line ( 'The responsibility added to the user '
v_user_name
' is '
v_req_resp_name);

COMMIT;

ELSE

DBMS_OUTPUT.put_line
('The responsibility has already been added to the user');

END IF;

END;

PL/SQL FUNCTION for converting Rupees in Words

CREATE OR REPLACE FUNCTION ruppee_to_word (amount IN NUMBER)
RETURN VARCHAR2
AS
   v_length   INTEGER         := 0;
   v_num2     VARCHAR2 (50)   := NULL;
   v_amount   VARCHAR2 (50)   := TO_CHAR (TRUNC (amount));
   v_word     VARCHAR2 (4000) := NULL;
   v_word1    VARCHAR2 (4000) := NULL;
   TYPE myarray IS TABLE OF VARCHAR2 (255);
   v_str      myarray         := myarray (' Thousand ', ' Lakh ', ' Crore ');
BEGIN
   IF ((amount = 0) OR (amount IS NULL))
   THEN
      v_word := 'zero';
   ELSIF (TO_CHAR (amount) LIKE '%.%')
   THEN
      IF (SUBSTR (amount, INSTR (amount, '.') + 1) > 0)
      THEN
         v_num2 := SUBSTR (amount, INSTR (amount, '.') + 1);
         IF (LENGTH (v_num2) < 2)
         THEN
            v_num2 := v_num2 * 10;
         END IF;
  
         v_word1 :=
               ' AND '
            || (TO_CHAR (TO_DATE (SUBSTR (v_num2, LENGTH (v_num2) - 1, 2),
                                  'J'),
                         'JSP'
                        )
               )
            || ' paise ';
         v_amount := SUBSTR (amount, 1, INSTR (amount, '.') - 1);
         v_word :=
               TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 2, 3),
                                 'J'
                                ),
                        'Jsp'
                       )
            || v_word;
         v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 3);


         FOR i IN 1 .. v_str.COUNT
         LOOP
            EXIT WHEN (v_amount IS NULL);
            v_word :=
                  TO_CHAR (TO_DATE (SUBSTR (v_amount, LENGTH (v_amount) - 1,
                                            2),
                                    'J'
                                   ),
                           'Jsp'
                          )
               || v_str (i)
               || v_word;
            v_amount := SUBSTR (v_amount, 1, LENGTH (v_amount) - 2);
         END LOOP;
      END IF;
   ELSE
      v_word := TO_CHAR (TO_DATE (TO_CHAR (amount, '999999999'), 'J'), 'JSP');
   END IF;
  
   v_word := v_word || ' ' || v_word1 || ' only ';
   v_word := REPLACE (RTRIM (v_word), ' ', ' ');
   v_word := REPLACE (RTRIM (v_word), '-', ' ');
   RETURN INITCAP (v_word);
END ruppee_to_word;

TEST SCRIPT:

SET serveroutput on;
BEGIN
   DBMS_OUTPUT.put_line (ruppee_to_word (1455555));
END;

Order to Cash O2C

In this article, we will go through the Order to Cash cycle. The below are the steps in short:

1.       Enter the Sales Order

2.       Book the Sales Order

3.       Launch Pick Release

4.       Ship Confirm

5.       Create Invoice

6.       Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)

7.       Transfer to General Ledger

8.       Journal Import

9.       Posting

Let's get into the details of each step mentioned above.

1.       Enter the Sales Order:

Navigation:

Order Management Super User Operations (USA)>Orders Returns >Sales Orders

Enter the Customer details (Ship to and Bill to address), Order type.

click on Lines Tab. Enter the Item to be ordered and the quantity required.

Line is scheduled automatically when the Line Item is saved.

Scheduling/unscheduling can be done manually by selecting Schedule/Un schedule from the Actions Menu.

You can check if the item to be ordered is available in the Inventory by clicking on Availability Button.

Save the work.

Underlying Tables affected:

In Oracle, Order information is maintained at the header and line level.

The header information is stored in OE_ORDER_HEADERS_ALL and the line information in OE_ORDER_LINES_ALL when the order is entered. The column called FLOW_STATUS_CODE is available in both the headers and lines tables which tell us the status of the order at each stage.

At this stage, the FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL is 'Entered'

2.       Book the Sales Order:

Book the Order by clicking on the Book Order button.

Now that the Order is BOOKED, the status on the header is change accordingly.

Underlying tables affected:

At this stage:

The FLOW_STATUS_CODE in the table OE_ORDER_HEADERS_ALL would be

 'BOOKED'

The FLOW_STATUS_CODE in OE_ORDER_LINES_ALL will be

 'AWAITING_SHIPPING'.

Record(s) will be created in the table WSH_DELIVERY_DETAILS with

RELEASED_STATUS='R' (Ready to Release)

Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS.

At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts

into MTL_DEMAND

3.       Launch Pick Release:

Navigation:

Shipping > Release Sales Order > Release Sales Orders.

Key in Based on Rule and Order Number

In the Shipping Tab key in the below:

Auto Create Delivery: Yes

Auto Pick Confirm: Yes

Auto Pack Delivery: Yes

In the Inventory Tab:

Auto Allocate: Yes

Enter the Warehouse

Click on Execute Now Button.

On successful completion, the below message would pop up as shown below.

Pick Release process in turn will kick off several other requests like Pick Slip Report,

Shipping Exception Report and Auto Pack Report

Underlying Tables affected:

If Autocreate Delivery is set to 'Yes' then a new record is created in the table WSH_NEW_DELIVERIES.

DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS.

The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to 'Y' (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is 'S' (Release to Warehouse).

4.       Pick Confirm the Order:

IF Auto Pick Confirm in the above step is set to NO, then the following should be done.

Navigation:

Inventory Super User > Move Order> Transact Move Order

In the HEADER tab, enter the BATCH NUMBER (from the above step) of the order. Click FIND. Click on VIEW/UPDATE Allocation, then Click TRANSACT button. Then Transact button will be deactivated then just close it and go to next step.

5.       Ship Confirm the Order:

Navigation:Order Management Super User>Shipping >Transactions.

Query with the Order Number.

Click On Delivery Tab

Click on Ship Confirm.

The Status in Shipping Transaction screen will now be closed.

This will kick off concurrent programs like.INTERFACE TRIP Stop, Commercial Invoice, Packing Slip Report, Bill of Lading

Underlying tables affected:

RELEASED_STATUS in WSH_DELIVERY_DETAILS would be 'C' (Ship Confirmed)

FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be "BOOKED"

FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be "SHIPPED"

6.       Create Invoice:

Run workflow background Process.

Navigation:Order Management >view >Requests

Workflow Background Process inserts the records RA_INTERFACE_LINES_ALL with

INTERFACE_LINE_CONTEXT     =     'ORDER ENTRY'

INTERFACE_LINE_ATTRIBUTE1=     Order_number

INTERFACE_LINE_ATTRIBUTE3=     Delivery_id

and spawns Auto invoice Master Program and Auto invoice import program which creates Invoice for that particular Order.

The Invoice created can be seen using the Receivables responsibility

Navigation:Receivables Super User> Transactions> Transactions

Query with the Order Number as Reference.

Underlying tables:

RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.

RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

7.       Create receipt:

Navigation:

Receivables> Receipts> Receipts

Enter the information.

Click on Apply Button to apply it to the Invoice.

Underlying tables:

AR_CASH_RECEIPTS_ALL

8.       Transfer to General Ledger:

To transfer the Receivables accounting information to general ledger, run General Ledger Transfer Program.

Navigation:

Receivables> View Requests

Parameters:

Give in the Start date and Post through date to specify the date range of the transactions to be transferred.

Specify the GL Posted Date, defaults to SYSDATE.

Post in summary: This controls how Receivables creates journal entries for your transactions in the interface table. If you select 'No', then the General Ledger Interface program creates at least one journal entry in the interface table for each transaction in your posting submission. If you select 'Yes', then the program creates one journal entry for each general ledger account.

If the Parameter Run Journal Import is set to 'Yes', the journal import program is kicked off automatically which transfers journal entries from the interface table to General Ledger, otherwise follow the topic Journal Import to import the journals to General Ledger manually.

Underlying tables:

This transfers data about your adjustments, chargeback, credit memos, commitments, debit memos, invoices, and receipts to the GL_INTERFACE table.

9.       Journal Import:

To transfer the data from General Ledger Interface table to General Ledger, run the Journal Import program from Oracle General Ledger.

Navigation: General Ledger > Journal> Import> Run

Parameters:

Select the appropriate Source.

Enter one of the following Selection Criteria:

No Group ID: To import all data for that source that has no group ID. Use this option if you specified a NULL group ID for this source.

All Group IDs: To import all data for that source that has a group ID. Use this option to import multiple journal batches for the same source with varying group IDs.

Specific Group ID: To import data for a specific source/group ID combination. Choose a specific group ID from the List of Values for the Specific Value field.

If you do not specify a Group ID, General Ledger imports all data from the specified journal entry source, where the Group_ID is null.

10.   Define the Journal Import Run Options (optional)

Choose Post Errors to Suspense if you have suspense posting enabled for your set of books to post the difference resulting from any unbalanced journals to your suspense account.

Choose Create Summary Journals to have journal import create the following:

• one journal line for all transactions that share the same account, period, and currency and that has a debit balance

• one journal line for all transactions that share the same account, period, and currency and that has a credit balance.

Enter a Date Range to have General Ledger import only journals with accounting dates in that range. If you do not specify a date range, General Ledger imports all journals data.

Choose whether to Import Descriptive Flexfields, and whether to import them with validation.

Click on Import button.

 Underlying tables:

GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES

11.   Posting:

We have to Post journal batches that we have imported previously to update the account balances in General Ledger.

Navigation:General Ledger> Journals > Enter

Query for the unposted journals for a specific period as shown below.

From the list of unposted journals displayed, select one journal at a time and click on Post button to post the journal.

If you know the batch name to be posted you can directly post using the Post window

Navigation:General Ledger> Journals> Post

Underlying tables:

GL_BALANCES.

PO,APS,GL,OM,INV,AR tables description

Purchase Order (PO) :------------------------
1.po_requisition_headers_all -- requisition header info
2.po_requisition_lines_all   -- Requisition Lines info
3.po_req_distributions_all   -- Requisition Distribution info
4.po_headers_all             -- PO Header Info
5.po_lines_all               -- PO Line Info
6.po_line_locations_all      -- PO Line Shipment info
7.po_distributions_all       -- PO Distribution info
8.rcv_shipment_headers       -- Receiving header info
9.rcv_shipment_lines         -- Receiving Lines info
10.rcv_transactions          -- Receiving transationd info
11.po_vendors                -- Supplier Header info
12.po_vendor_sites_all       -- Supplier Site info
13.po_vendor_site_contacts   -- Supplier Site Contact info
14.hr_locations              -- Supplier Site Address

Order Management (OM) :-----------------------------
1.oe_order_headers_all    -- Order Header info
2.oe_order_lines_all      -- Order line info
3.oe_transaction_types_tl -- Order type info
4.oe_order_holds          -- Order Hold info
4.oe_holds_all              -- Order Hold info
5.oe_hold_sources           -- Order Hold source info
6.oe_hold_releases        -- Hold Release info
7.wsh_delivery_details    -- Delivery Detial Info
8.wsh_new_deliveries      -- Delivery Header info
9.wsh_delivery_Assignments -- Delivery Assignments info
10.wsh_trip_stops          -- Delivery trips info
11.hz_cust_accounts        -- Customer info
12.hz_parties              -- Party info
13.hz_cust_site_uses_all   -- Customer site use info
14.hz_cust_acct_sites_all  -- Customer Site Acct info
15.hz_party_sites          -- Party site info
16.hz_locations            -- Customer Site Adderess
17.wsh_lookups             -- Shipping lookup info

Accounts Payable (AP):-------------------------
1.ap_invoices_all               -- Invoice Header info
2.ap_invoice_distributions_all  -- Invoice Line info
3.ap_checks_all                 -- Check info
4.ap_invoice_payments_all       -- Invoice Payment info
5.ap_payment_schedules_all      -- Payment Schedule info
6.ap_holds_all                  -- Invoice Holds info
7.ap_lookup_codes               -- Payable lookup info
8.po_vendors                -- Supplier Header info
9.po_vendor_sites_all       -- Supplier Site info
10.po_vendor_site_contacts   -- Supplier Site Contact info
11.hr_locations              -- Supplier Site Address
12.ap_banks                  -- Bank Info
13.ap_bank_branches          -- Bank Branch info
14.ap_ae_headers_all         -- Accounitng header info
15.ap_ae_lines_all           -- Accounting Lines info
16.ap_ae_accounting_evets    -- Accounting events info
17.ap_terms                  -- Payment Terms

Accounts Receivables (AR) :-------------------------------
1.ra_customer_trx_all     -- Receivable transaction info
2.ra_customer_trx_lines_all -- Transaction lines info
3.ra_cust_trx_line_gl_dist_all -- Transaction distribution info
4.ar_receivable_applications_all -- Receiving application info
5.ar_cash_Receipts_all           -- Cash Receipt info
6.ar_terms                       -- Receivable Terms
7.hz_cust_accounts        -- Customer info
8.hz_parties              -- Party info
9.hz_cust_site_uses_all   -- Customer site use info
10.hz_cust_acct_sites_all  -- Customer Site Acct info
11.hz_party_sites          -- Party site info
12.hz_locations            -- Customer Site Adderess

Invenvtory (INV) Module :-----------------------------
1.mtl_System_items_b    -- Master item info
2.mtl_onhand_quanitties -- Item onhand qty info
3.mtl_reservations      -- Item Reservation info
4.mtl_material_transactions -- Item Transaction info
5.mtl_item_locations         -- Item location info
6,mtl_Categeries             -- Item Category info
7.mtl_item_categories        -- Invemtry Categry
8.mtl_secondary_inventories  -- Subinventories info
9.org_organization_definitions -- Organizaition info
10.mtl_transaction_Accounts    -- Item Transaction info
11.mtl_txn_source_types        -- Item Transaction sources
12.mtl_parameters              -- Inventory Parameters

General Ledger(GL) :-----------------------
1.gl_je_headers    -- Journal Header info
2.gl_je_lines      -- Journal Line info
3.gl_je_batches    -- Journal Batch info
4.gl_Sets_of_books -- set of books info
5.gl_chart_of_accoutns -- chart of accounts info
6.gl_code_combinations -- Code combination info
7.gl_je_sources        -- Journal Source info
8.gl_je_categories     -- Journal Cateogiy info
9.fnd_currencies       -- Currency info
10.gl_balances         -- Journal Balances

Using UTL_FILE Package (Load the data into the table)

CREATE OR REPLACE package body APPS.xx_po_pu_det_pkg
is
/*
Procedure to read data from flat file
*/
    procedure pur_dat_prc(x_errbuf OUT VARCHAR2
                        ,X_RETCODE OUT VARCHAR2
                        ,P_FILE_PATH IN VARCHAR2
                        ,P_FIL_NAME IN VARCHAR2
                        )
is
v_file_type         utl_file.file_type;
v_data              varchar2(1000);
v_vendor_number         po_vendors.segment1%type;
v_vendor_name           po_vendors.vendor_name%type;
v_vendor_site_code      po_vendor_sites_all.vendor_site_code%type;
v_po_number             po_headers_all.segment1%type;
begin
    v_file_type := utl_file.fopen(P_FILE_PATH,P_FIL_NAME,'R');
    loop
        begin
 --       fnd_file.put_line(fnd_file.output,'Start Loop');
        utl_file.get_line(v_file_type,v_data);
        fnd_file.put_line(fnd_file.output,'Data => '||v_data);
    select substr(v_data,1,instr(v_data,',',1)-1)
    into v_vendor_number
    from dual;
   
    select substr(v_data,instr(v_data,',',1,1)+1,instr(v_data,',',1,2)-(instr(v_data,',',1,1)+1))
    into v_vendor_name
    from dual;
    select substr(v_data,instr(v_data,',',1,2)+1,instr(v_data,',',1,3)-(instr(v_data,',',1,2)+1))
    into v_vendor_site_code 
    from dual;
    select substr(v_data,instr(v_data,',',1,3)+1,length(v_data)-(instr(v_data,',',1,3)))
    into v_po_number
    from dual;
    insert into XX_PO_PUR_DET_STG
    values(
    v_vendor_number
    ,v_vendor_name
    ,v_vendor_site_code
    ,v_po_number
    );
   
    exception
        when utl_file.invalid_path then
            fnd_file.put_line(fnd_file.output,'Invalid file path');       
        when utl_file.invalid_mode then
            fnd_file.put_line(fnd_file.output,'Invalid Mode');
        when utl_file.invalid_filehandle then
            fnd_file.put_line(fnd_file.output,'Invalid file handle');
        when utl_file.invalid_operation then
            fnd_file.put_line(fnd_file.output,'Invalid file operation');
        when utl_file.read_error then
            fnd_file.put_line(fnd_file.output,'Read error');
        when no_data_found then
            exit;
        when others then
           fnd_file.put_line(fnd_file.output,'Others exception => '||SQLERRM);    
        end;
    end loop;
--    fnd_file.put_line(fnd_file.output,'after end loop');
    utl_file.fclose(v_file_type);
--    fnd_file.put_line(fnd_file.output,'after close');
exception
    when others then
        fnd_file.put_line(fnd_file.log,'Exception in procedure pur_dat_prc => '||SQLERRM);
end pur_dat_prc;           
end xx_po_pu_det_pkg;
/

Sample code of UTL_FILE package for out bound

CREATE OR REPLACE procedure
APPS.xx_po_out(x_errbuf out varchar2
,p_retcode out varchar2
,p_file_path in varchar2
,p_file_name in varchar2
)
is
g_org_id number := fnd_profile.value('ORG_ID');
g_conc_request_id number := fnd_profile.value('CONC_REQUEST_ID');
cursor cur_podet
is
select vendor_name
,pov.segment1 vendor_number
,povs.VENDOR_SITE_CODE
,povs.ADDRESS_LINE1||' '||povs.ADDRESS_LINE2 address
,povs.country
,poh.SEGMENT1 po_number
from po_vendors pov,
po_headers_all poh,
po_vendor_sites_all povs
where pov.vendor_id = poh.vendor_id
and poh.vendor_site_id = povs.vendor_site_id
and poh.org_id = g_org_id;
v_file utl_file.file_type;
v_file_name varchar2(100) ;

begin
    fnd_file.put_line(fnd_file.log,'Concurrent Request Id => '||p_file_name||'_'||g_conc_request_id||'.txt');
    v_file_name  := p_file_name||'_'||g_conc_request_id||'.txt';
    v_file := utl_file.fopen(p_file_path,v_file_name ,'W');
    for rec_podet in cur_podet
    loop
        begin
        utl_file.PUT_LINE(v_file,
         rec_podet.vendor_name
        ||','||rec_podet.vendor_number
        ||','||rec_podet.VENDOR_SITE_CODE
        ||','||rec_podet.address
        ||','||rec_podet.country
        ||','||rec_podet.po_number
        );
    exception
        when utl_file.invalid_path then
            fnd_file.put_line(fnd_file.log,'Invalid Path');
        when utl_file.invalid_mode  then
            fnd_file.put_line(fnd_file.log,'Invalid Mode');
        when utl_file.invalid_filehandle then
            fnd_file.put_line(fnd_file.log,'Invalid file handle');
        when utl_file.invalid_operation  then
            fnd_file.put_line(fnd_file.log,'Invalid Operation');
        when utl_file.write_error        then
            fnd_file.put_line(fnd_file.log,'Write error');
        when others then
            fnd_file.put_line(fnd_file.log,'exception in loop => '||SQLERRM);
        end;   
    end loop;
    utl_file.FCLOSE(v_file);
exception
    when others then
        fnd_file.put_line(fnd_file.log,'exception in procedure => '||SQLERRM);   
end ;

How to delete a Descriptive Flex Field DFF Context

Sometime I'm quite annoyed by the typo mistake when creating a DFF context. The DFF segment screen doesn't allow deletion of context. Fortunately, Oracle has internal API to do such thing. Following is a sample.
--*******************************************
--* Delete a descriptive flexfield
--*******************************************
SET ECHO OFF
SET FEEDBACK OFF
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_application_id                NUMBER := 0;
  l_descriptive_flexfield_name    VARCHAR2(100) :=  'FND_COMMON_LOOKUPS' ;
  l_descriptive_flex_context_cod  VARCHAR2(100) :=  'XFND_CLWW_PURGE_FOLDER';
BEGIN
  --FND_DESCRIPTIVE_FLEXS_PKG --this package is for DFF
  --FND_DESCR_FLEX_CONTEXTS_PKG --this package is for DFF Context
  --FND_DESCR_FLEX_COL_USAGE_PKG --this package is for DFF Column useage
  --When creating a new DFF Context, it will check the DFF Column usage if the context is already used.
  --so when deleting a DFF Context, both the context and column usage should be deleted.
  FOR c IN (SELECT application_column_name
              FROM fnd_descr_flex_column_usages
             WHERE application_id = l_application_id
               AND descriptive_flexfield_name = l_descriptive_flexfield_name
               AND descriptive_flex_context_code = l_descriptive_flex_context_cod)
  LOOP
 
    fnd_descr_flex_col_usage_pkg.delete_row(
         x_application_id                => l_application_id
        ,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
        ,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
        ,x_application_column_name       => c.application_column_name
        );
  END LOOP;
  fnd_descr_flex_contexts_pkg.delete_row(
         x_application_id                => l_application_id,
        ,x_descriptive_flexfield_name    => l_descriptive_flexfield_name
        ,x_descriptive_flex_context_cod  => l_descriptive_flex_context_cod
         );
 
  --commit;
end;

How to compile the form in 11i & R12

R12:

frmcmp_batch userid=apps/apps module=XXXLPNINSPECTION.fmb output_file=XXXCLPNINSPECTION.fmx module_type=form batch=no compile_all=yes

11i:

f60gen module=XX_ARXSUPST.fmb userid=apps/apps@dev module_type=form batch=no compile_all=special
f60gen module=CUSTOM.pll userid=apps/apps@uat module_type=LIBRARY batch=NO compile_all=special

What Actions are supported in import standard purchase order ?

ORIGINAL - create a new document
ADD - add new lines onto an existing document
UPDATE - update existing line information for a document
REPLACE - Replace the existing document
Which interface tables are involved?

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE(Used for Standard PO only)

Purchasing Interface Errors Report(choose parameter : PO_DOCS_OPEN_INTERFACE)

Check the process_code in the po_headers_interface and po_lines_interface, if it is 'REJECTED',

select process_code from po_headers_interface;
select process_code from po_lines_interface;

please do the following:
Run the program - Purchasing Interface Errors Report
choose parameter : PO_DOCS_OPEN_INTERFACE

The report will list all the errors you have during importing. You can fix the data, then reset process_code = Null in both interface tables, rerun the Purchasing Document Open Interface.

update po_headers_interface set process_code = null
where process_code = 'REJECTED';
update po_lines_interface set process_code = null
where process_code = 'REJECTED';

How to deleted from the interface tables after it has been loaded in import standard purchase order

After loading the data from the interface tables into the system successfully,  the data is not being removed (cleaned) from the interfaces.
They will have process_code = 'ACCEPT' in the interface tables.

To remove the processed data from the interface, a concurrent program is available.  The program name is : purge purchasing open interface processed data(POXPOIPR)

Run this program with paramenter purge accepted data = Yes
Then the process_code = 'ACCEPTED' records will be removed from the interface tables.

Create directory in Oracle

Create directory in Oracle       

create or replace directory foo_dir as '/tmp';

Directories must be created if external tables are used.
Created directories are shown in either dba_directories or all_directories. There is no user_directories.

Privileges

When a «directory» has been created, the read and write object privileges can be granted on it:

create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

An example

The following example shows how create directory and utl_file can be used to write text into a file:

create or replace directory dir_temp as 'c:\temp';

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/

Query for finding Request Group

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name,
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE     frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id
       AND fe.executable_id = fcp.executable_id
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = 'XXTPC_PC_DOLR_PURCHASE';

Report output file send to user

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

#|========================================================================================+
#|  INDUSTRIEES. |
#+========================================================================================+
# |
# Filename: REPORT_OUT_EMAIL_SH.prog |
# |
# Description: This script is developed to send the Shipping/Invoice Report |
# output to Email Id.The outfile taken from out directory is |
# sent to the EMAIL Specified. |
# |
# Revision History: |
# |
# Ver Date Author Revision Description |
# === ============ ==================== ==================== |
# 1.0 27-Apr-2011 Mahammad Sulthan Created the program |
# |
# |
# |
# |
#=========================================================================================+
#Parameters from 1 to 4 i.e $1 $2 $3 $4 are standard parameters
# $1 : username/password of the database
# $2 : userid
# $3 : USERNAME
# $4 : Concurrent Request ID
echo "Start of the Program"
EMAIL=`echo $1 | cut -d " " -f9`
P_EMAIL=`echo $EMAIL | cut -d '"' -f2`
PATH_NAME=`echo $1 | cut -d " " -f10`
P_DIR=`echo $PATH_NAME | cut -d '"' -f2`
P_PARENT_REQUEST_ID=`echo $1 | cut -d '"' -f12`
echo "Parameters passed :"
echo "================================================= \n"
echo "Mail id :" $P_EMAIL
echo "Outfile Directory :" $P_DIR
echo "Parent Request Id : $P_PARENT_REQUEST_ID "
echo "================================================ \n"
P_OUT_FILE_NAME="ONTCDELRI_"${P_PARENT_REQUEST_ID}"_1.PDF"
echo " Email File Name : $P_OUT_FILE_NAME "

sqlplus -s $FCP_LOGIN <<!
DECLARE
A NUMBER;
X_req_id number;
X_dev_status VARCHAR2(30) := NULL;
X_phase VARCHAR2(30) := NULL;
X_status VARCHAR2(30) := NULL;
X_dev_phase VARCHAR2(30) := NULL;
X_message VARCHAR2(200) := NULL;
BEGIN
loop
IF FND_CONCURRENT.WAIT_FOR_REQUEST
( request_id => ${P_PARENT_REQUEST_ID}
,interval => 300
,max_wait => 300
,phase => X_phase
,status => X_status
,dev_phase => X_dev_phase
,dev_status => X_dev_status
,message => X_message
)
THEN
exit;
END IF;
end loop;
END;
/

!
#---------------------------------------------------------------------------------------------------
# Checking if output file exists. If yes, then mailing them else displaying no files found
#---------------------------------------------------------------------------------------------------
if test -f $P_DIR/$P_OUT_FILE_NAME;
then
body=" Hi, \n \n This is an automated email, please do not reply to this email. Contact Helpdesk/Support Team for any further help. \n \n Thank You!"
(echo $body;uuencode "$P_DIR/$P_OUT_FILE_NAME" "$P_OUT_FILE_NAME") | mailx -m -s "Delivery Note Report" $P_EMAIL
echo "Report is completed and EMAIL has been sent to $P_EMAIL Successfully. "
else
echo "No Output files found to be sent in the mail"
fi
echo "****************************************************************"

XML Publisher and Data Template - SQL Query to develop BI Publisher

The XML Publisher(XMLP / BI Publisher ) requires a Template layout and Data XML to generate the report in
PDF, EXCEL, RTF or HTML format .The Data XML can be generated from Oracle Report, OA Framework and Data Template.
Using this approach, you can develop BI Publisher reports in Oracle EBS, without having to develop an RDF report.
You can specify an SQL Statement in an XML file, and results of that SQL Statement are published as XMLP Output.

In this article lets understand the usage of Data Template with a small demo. In the next article we will discuss in detail the Data Template XML Schema.

The data template is the XML document whose elements communicate the following information to the data engine.

• Data Query : The SQL query with bind parameter specifying the data to be fetched.
• Bind Parameters : Definition of each bind parameter defined in the query.
• Data Structure : Definition of the Output XML Structure.
• Data Triggers : Triggers that should be executed before or after the data query execution. Triggers are used to either do initialization or do some post query operation.

Hence Data Template is the means by which we inform the data engine to fetch the data of our interest. Apart from the these , data template also contains other elements. To make our example simple and easy to understand, lets deal with the above 4 elements.

Sample Data template :

XML Publisher Data Template Architecture

The Data engine execute the Data template to generate the Data XML file . The Data XML file is then merged with the Template layout by the XMLP engine to generate the XMLP
Report.

Lets try to build a sample XMLP report to display Employee Details. The Following are the steps involved

Step 1: Design the Data Template XML File.
Step 2: Create the Data Definition in XML Publisher and associate it with Data Template
Step 3: Define a Concurrent Program to generate the Data XML File.
Step 4: Define the RTF Template using the Generated Data XML.
Step 5: Registering the Template with XML Publisher
Step 6: Execute the concurrent program to generate the PDF report.

Step 1 : Design the Data Template XML File.

This Data Template selects the employee details from the EMP table. The select query specifies the columns of interest. It uses a bind parameter to filter the employees of a particular department.

For each bind parameter in the query , we need to define a Parameter. In this case we have defined p_DeptNo parameter.

In the data structure element, we have defined the output XML structure. He we map the columns in the select query with the data xml elements.

Step 2 : Create the Data Definition in XML Publisher and associate it with Data Template
Using Responsibility: XML Publisher Administrator --> Home --> Data Definitions --> Create Data Definition

Screen 1 : Enter the data definition Details and click on Apply. Note down the Code. The code should be used as the short name of the concurrent program.

Screen 2: Click on the "Add File" button against the "Data Template" Label.

Screen 3: Upload the Data Template XML file and Click on Apply.
Screen 4 : The Data Template is associated to the Data Definition..

Step 3: Define a Concurrent Program to generate the Data XML File.

Using Responsibility System Administrator --> Concurrent : Program --> Define.

Screen 1: Define a Concurrent Program With executable = XDODTEXE and Output Format = XML.

Screen 2: For each parameter in the Data Template, define a parameter in the concurrent program. The Data Template parameter name should match the concurrent program parameter token.

Screen 3: Associate the Concurrent Program to a request group.




Screen 4 : Execute the concurrent program "Employee Details Data Template Demo"and click on the output button get the Data XML. Save the XML file. We will use it to generate the RTF Template.

Step 4: Define the RTF Template using the Generated Data XML.

Install the Oracle XML Publisher Desktop available via patch 5887917. Open the Microsoft word. You should be able to see the following menus and toolbars.

Using the menu Data -> Load XML Data... , load the XML File generated from Jdeveloper

If the XML files gets loaded successfully, then you should get the below confirmation.

Using the Table Wizard as below to create the 'Table Report Format' with all the columns of EMP.

The Table Report Format Template should be like

Step 5: Registering the Template with XML Publisher
Responsibility: XML Publisher Administrator --> Home --> Templates --> Create Template.
Enter the Template Name and other details. The Data Definition should be the data definition defined in the above steps. Click on the apply button

Step 6 : Execute the concurrent program to generate the PDF report.
Open the concurrent program for submission and click on the option button to specify the XML Publisher Template defined in the previous step and Output type to be used. The options specified once gets stored . Hence if open the option window again, the template name and the output type will be available. Click ok button and run the concurrent program. When the program completes. Click on the Output button to see the PDF report.

XML Publisher using a Data Template and XDODTEXE

Create a concurrent program: Use the output format as XML

Define a Data Definition for the concurrent program:

Concurrent program short name and Data definition code has to be same.

Create the data template file and upload it while defining in the data definition

Attach the concurrent program to a responsibility:

Run the concurrent program:

Save the output as .XML file:

Use the .XML file to design the template definition: Save the file as .rtf file

Create the template definition with the .rtf file:

Change the concurrent program output format to PDF:

Run the concurrent program: