Oracle APPS for Technical's

Oracle ERP HandBook by Chaitanya

2013-10-02T08:11:00.001-07:00

----------------------------------------------------------------------------------------
-- Purpose : Oracle ERP HandBook
-- Release Date: 03.Jun.2011
-- Created by : Siri
---------------------------------------------------------------------------------------

Oracle APPS Technical Handbook gives a complete hands on experience for freshers.

The link is now disabled Oracle ERP Handbook.pdf, Contact kchaitanya.v@gmail.com for the same.

Contents of the Oracle ERP Handbook.pdf is mentioned below.

Write to us to get more updates on oracle technologies.

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

2013-09-29T23:25:00.002-07:00

Watch this Space to get more updates on Fusion HCM

Thanks,

Chaitanya

Fixed Asset details - R12

2012-07-12T23:51:00.003-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : R12 Fixed Asset details
-- Release Date: 13.Jul.2012
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select fab.asset_id     asset_id,
fab.asset_number     asset_number,
fal.description     asset_description,
fab.current_units     units,
(select sum(fixed_assets_cost)
from apps.fa_invoice_details_v
where asset_id = fab.asset_id)   cost,
fab.serial_number     serial_number,
fab.model_number     model_number,
fab.tag_number     tag_number,
(select ASSIGNMENT_NUMBER
from apps.per_all_assignments_f
where person_id = fdh.assigned_to
and trunc(sysdate) between effective_start_date and effective_end_date
)     assigned_to
from apps.fa_additions_b fab,
apps.fa_distribution_history fdh,
apps.fa_additions_tl fal
where 1=1
and fab.asset_id = fdh.asset_id
and fal.asset_id = fab.asset_id
and trunc(sysdate) between fdh.date_effective and nvl(fdh.date_ineffective,sysdate+1);

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

To get the Internal BankAccount / Bank Branch Details - R12

2012-07-12T23:47:00.001-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : R12 To get the Internal BankAccount / Bank Branch Details
-- Release Date: 13.Jul.2012
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select ieba.ext_bank_account_id,
ieba.bank_account_num,
ieba.bank_account_name,
ieba.bank_id,
ieba.branch_id,
ieb.bank_name,
iebb.bank_branch_name,
iebb.branch_number,
iebb.eft_swift_code,
iebb.address_line1,
iebb.address_line2,
iebb.address_line3,
iebb.address_line4,
iebb.city,
iebb.state,
iebb.country,
iebb.zip,
ieba.creation_date,
(select user_name from fnd_user where user_id = ieba.created_by) created_by
from
apps.iby_ext_bank_branches_v iebb,
apps.iby_ext_banks_v ieb,
apps.iby_ext_bank_accounts ieba
where iebb.bank_party_id = ieba.bank_id
and iebb.branch_party_id = ieba.branch_id
and ieb.bank_party_id = iebb.bank_party_id
and ieb.bank_party_id = ieba.bank_id

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

To get the selected invoices for the payment batch

2009-09-22T10:52:00.000-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : 11i To get the selected invoices for the payment batch, After invoice selection Screen
-- Release Date: 22.Sep.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select ai.INVOICE_NUM,
ai.INVOICE_AMOUNT,
ai.AMOUNT_PAID,
asi.CHECKRUN_NAME,
asi.CHECK_NUMBER,
asi.AMOUNT_REMAINING,
abb.BANK_NAME,
abb.BANK_BRANCH_NAME,
asi.BANK_ACCOUNT_NUM,
asi.BANK_ACCOUNT_TYPE
from ap_selected_invoices_all asi,
AP_BANK_ACCOUNTS_all aba,
AP_BANK_BRANCHES abb,
ap_invoices_all ai
where 1=1
and asi.checkrun_name = :Payment_Batch_name
and asi.INVOICE_ID = ai.INVOICE_ID
and aba.BANK_ACCOUNT_NUM = asi.BANK_ACCOUNT_NUM
and aba.BANK_ACCOUNT_TYPE = asi.BANK_ACCOUNT_TYPE
and aba.BANK_BRANCH_ID = abb.BANK_BRANCH_ID
order by 4,1;

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

Query to get all the period statuses for all the applications

2009-08-03T08:49:00.000-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : To get current period statuses for all the application modules at a single query
-- Release Date: 03.Aug.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select gsob.NAME sob_name,
fav.APPLICATION_NAME,
gps.PERIOD_NAME,
gps.start_date,
gps.END_DATE,
(select meaning from fnd_lookups
where lookup_code = gps.CLOSING_STATUS
and lookup_type = 'IGC_CC_PERIOD_STATUS') Period_status
from GL_PERIOD_STATUSES gps,
fnd_application_vl fav,
gl_sets_of_books gsob
where gps.APPLICATION_ID = fav.APPLICATION_ID
and gsob.set_of_books_id = gps.SET_OF_BOOKS_ID
and fav.APPLICATION_SHORT_NAME = nvl(:appl_short_name,fav.APPLICATION_SHORT_NAME)
and gps.PERIOD_NAME = nvl(:p_period,to_char(sysdate,'Mon-YY'))
order by 1,2

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

Query to get the list of all profiles defined at different levels

2009-07-28T10:44:00.000-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Query to get the List of all profiles defined at different levels
-- Release Date: 28.July.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select fpo.profile_option_name Profile_name
, fpot.user_profile_option_name User_Profile_Name
, fpot.description Description
, fpo.start_date_active Start_date
, fpo.end_date_active End_Date
, fpo.creation_date Creation_Date
, fu.user_name created_by
, fpov.LAST_UPDATE_DATE Updated_Date
, (select user_name from fnd_user fu1
where fpov.LAST_UPDATEd_by = fu1.user_id) Updated_by
, decode(fpov.level_id ,10001, 'Site',10002, 'Application',10003, 'Responsibility',10004, 'User') Profile_Level
, fpov.profile_option_value Profile_option_value
from FND_PROFILE_OPTIONS_vl fpot
, FND_PROFILE_OPTIONS fpo
, FND_PROFILE_OPTION_VALUES fpov
, FND_USER fu
where fpot.profile_option_name = fpo.profile_option_name
and fpo.application_id = fpov.application_id
and fpo.profile_option_id = fpov.profile_option_id
and fpo.created_by = fu.user_id
and fpot.language = Userenv('Lang')
order by fpov.level_id;

Query to get the account details for a particular payable bank

2009-07-28T10:38:00.001-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Query to get the account details for a particular payable bank
-- Release Date: 28.July.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

select abb.bank_name,
aba.BANK_ACCOUNT_NAME,
aba.BANK_ACCOUNT_NUM,
aba.DESCRIPTION,
aba.CURRENCY_CODE curr_code
from AP_BANK_branches abb,
AP_BANK_ACCOUNTS_ALL aba
where abb.BANK_BRANCH_ID = aba.BANK_BRANCH_ID
and abb.bank_name = :Bank_name

Oracle Discoverer Handbook

2009-06-03T00:00:00.000-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Discoverer Administrator / Desktop Handbook.
-- Release Date: 03.June.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

Oracle Discoverer Handbook gives a complete hands on experience in Oracle Discoverer Administrator and Discoverer Desktop.

Get the book download from this link Oracle Discoverer Handbook.pdf covers all areas in discoverer desktop and discoverer administrator.

Write to us to get more updates on oracle technologies.

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

Inventory Stock details of an item

2009-06-01T06:34:00.000-07:00

-- ----------------------------------------------------------------------------------------------------
-- Purpose : Inventory Stock details of an item by Subinventory / Oranizations
-- Date : 01.June.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------

SELECT
msi.SEGMENT1 Item_name,
ood.ORGANIZATION_NAME org_name,
msinv.SECONDARY_INVENTORY_NAME subinv_name,
moq.TRANSACTION_QUANTITY
from
mtl_system_items_b msi,
mtl_onhand_quantities moq,
mtl_secondary_inventories msinv,
org_organization_definitions ood
where msi.INVENTORY_ITEM_ID = moq.INVENTORY_ITEM_ID
and msinv.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msi.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msinv.SECONDARY_INVENTORY_NAME = moq.SUBINVENTORY_CODE
and ood.ORGANIZATION_ID = moq.ORGANIZATION_ID
and msi.segment1 = :item_name;

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

AR Invoice Interface rejected transactions

2009-05-31T08:00:00.000-07:00

-- ---------------------------------------------------------------------------
-- Purpose : AR Invoice Interface rejected Records for Analysis
-- Date : 31.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------

SELECT ril.INTERFACE_LINE_ID,
(select customer_name from ra_customers
where customer_id = ril.ORIG_SYSTEM_BILL_CUSTOMER_ID
or customer_number = ril.orig_system_bill_customer_ref) Customer_Name,
ril.trx_number,
ril.trx_date,
ril.gl_date,
ril.INTERFACE_LINE_CONTEXT,
ril.INTERFACE_LINE_ATTRIBUTE1,
ril.BATCH_SOURCE_NAME,
ril.LINE_TYPE,
ril.INTERFACE_STATUS Status ,
rie.INVALID_VALUE,
rie.MESSAGE_TEXT
from ra_interface_lines_all ril,
ra_interface_errors_all rie
where rie.INTERFACE_LINE_ID = ril.INTERFACE_LINE_ID
and ril.BATCH_SOURCE_NAME = :batch_source_name;

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

PID and SID details of a Concurrent Request

2009-05-30T04:19:00.001-07:00

-- ----------------------------------------------------------------------------------------------------------------
-- Purpose : Getting the Process ID, session and terminal details for the Concurrent request.
-- Date : 30.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------------------------------------

SELECT req.request_id ,
prog.USER_CONCURRENT_PROGRAM_NAME Conc_prg_Name,
fu.USER_NAME requested_by,
fr.RESPONSIBILITY_KEY responsibility,
ses.LOGON_TIME,
ses.sid,
ses.serial#,
proc.SPID,
proc.pid,
ses.USERNAME "User",
ses.STATUS,
ses.sql_hash_value sql_hash,
proc.USERNAME osuser,
ses.MACHINE,
nvl(ses.TERMINAL,'Not Available') Terminal,
ses.MODULE,
ses.ACTION,
ses.MODULE_HASH,
nvl(ses.PROGRAM,'Not Available') Program
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_responsibility fr,fnd_user fu,
fnd_executables execname
where
1=1
and req.request_id = :request_id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and req.RESPONSIBILITY_ID = fr.RESPONSIBILITY_ID
and fu.USER_ID = req.REQUESTED_BY
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

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

Purchase order and Receipt Information

2009-05-29T10:23:00.000-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Get the details of the PO, Receipt and line status information
-- Date : 29.May.2009
-- Created by : Siri
-- .....
-- ----------------------------------------------------------------------------------

SELECT ph.segment1 po_num,
pl.LINE_NUM,
msi.segment1 item_name,
pv.VENDOR_NAME,
pll.QUANTITY ord_qty,
pll.QUANTITY_RECEIVED qty_rcvd,
pll.QUANTITY_BILLED Invcd_qty,
decode((select count(distinct rsh.RECEIPT_NUM) from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID),1,
(select distinct rsh.RECEIPT_NUM from RCV_SHIPMENT_lines rsl,
rcv_shipment_headers rsh
where rsl.po_line_id = pl.PO_LINE_ID
and rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID)
,'Received in More than one Receipt') Receipt_num,
pll.PO_LINE_ID,
ph.CLOSED_CODE order_status,
pl.CLOSED_CODE line_status
from po_headers_all ph
,po_lines_all pl
,po_line_locations_all pll
,po_vendors pv
,mtl_system_items_b msi
where 1=1
and ph.type_lookup_code = 'STANDARD'
and pl.PO_HEADER_ID = ph.PO_HEADER_ID
and pll.PO_HEADER_ID = ph.PO_HEADER_ID
and pl.PO_LINE_ID = pll.PO_LINE_ID
and msi.ORGANIZATION_ID = pll.SHIP_TO_ORGANIZATION_ID
and ph.VENDOR_ID = pv.VENDOR_ID
and msi.INVENTORY_ITEM_ID = pl.ITEM_ID
and ph.SEGMENT1 = :po_num;

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

AP Invoice Interface rejected Records

2009-05-28T12:10:00.000-07:00

-- ---------------------------------------------------------------------------
-- Purpose : AP Invoice Interface rejected Records for Analysis
-- Date : 28.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------

SELECT pv.vendor_name,
aii.INVOICE_ID,
aii.SOURCE,
aii.invoice_num,
(select decode(count(*),0,'Invoice N/A','Invoice Available') from ap_invoices_all
where org_id = aii.ORG_ID
and invoice_num = aii.INVOICE_NUM) Inv_in_Base,
aii.CREATION_DATE,
aii.invoice_date,
aii.GL_DATE,
ail.LINE_NUMBER,
aii.REQUEST_ID,
aii.invoice_currency_code,
nvl(airh.REJECT_LOOKUP_CODE,airl.REJECT_LOOKUP_CODE) Rejection_Reason,
(select decode(count(INACTIVE_DATE),0,'Site is Active','Site is Inactive') from po_vendor_sites_All
where vendor_site_id = aii.VENDOR_SITE_ID) Site_STatus
from ap_invoice_lines_interface ail,
AP_INTERFACE_REJECTIONS airh,
AP_INTERFACE_REJECTIONS airl,
ap_invoices_interface aii,
po_vendors pv
where aii.INVOICE_ID = ail.INVOICE_ID
and airh.PARENT_ID = aii.INVOICE_ID
and ail.INVOICE_LINE_ID = airl.PARENT_ID
and aii.VENDOR_ID = pv.VENDOR_ID
order by 2,6;

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

Purchase Order Cancellation Interface

2009-05-26T13:40:00.000-07:00

-- ---------------------------------------------------------------------------
-- Purpose : Purchase order Cancellation validations
-- Date : 26.May.2009
-- Created by : Siri
-- .....
-- ---------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE xx_po_can_validation(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
l_count NUMBER(2):=0;
l_header_id NUMBER:=0;
l_line_id NUMBER:=0;
l_flag VARCHAR2(1):=NULL;
CURSOR c_po_cancel_valid IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status IN ('E','T')
FOR UPDATE;
BEGIN
FOR c_po_cancel_valid_rec IN c_po_cancel_valid
LOOP
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_count=0 THEN
UPDATE xx_tab_po_can_stg
SET row_status='E'
,error_message='This Slip number,Po number is not existing'
WHERE CURRENT OF c_po_cancel_valid;
ELSIF l_count>0 THEN
SELECT pla.cancel_flag INTO l_flag
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
IF l_flag='Y' THEN
UPDATE xx_tab_po_can_stg
SET row_status='R'
,error_message='This slipno and pono combination is already closed'
WHERE CURRENT OF c_po_cancel_valid;
ELSE
SELECT pha.po_header_id
,pla.po_line_id
INTO l_header_id
,l_line_id
FROM po_headers_all pha
,po_lines_all pla
WHERE pha.po_header_id=pla.po_header_id
AND UPPER(pha.attribute1)=UPPER(c_po_cancel_valid_rec.po_header_reference)
AND UPPER(pla.attribute1)=UPPER(c_po_cancel_valid_rec.po_line_reference);
UPDATE xx_tab_po_can_stg
SET header_id=l_header_id
,line_id=l_line_id
,row_status='V'
WHERE CURRENT OF c_po_cancel_valid;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_validation;
/

--======================================================
--Cancelling the Valid Purchase order lines
--======================================================

CREATE OR REPLACE PROCEDURE xx_po_can_import(errbuf out varchar2, retcode out varchar2)
AS
i NUMBER:=0;
x NUMBER:=0;
x_return_status VARCHAR2(1);
p_look_up_code VARCHAR2(10);
p_user_id NUMBER:=0;
p_resp_id NUMBER:=0;
p_resp_appl_id NUMBER:=0;
CURSOR c_po_cancel_import IS
SELECT *
FROM xx_tab_po_can_stg
WHERE row_status='V';
BEGIN
SELECT meai_po_cancel_seq.NEXTVAL INTO x
FROM DUAL;
SELECT fnd_profile.value('user_id') INTO p_user_id from dual;
SELECT fnd_profile.value('resp_id') INTO p_resp_id from dual;
SELECT fnd_profile.value('resp_appl_id') INTO p_resp_appl_id from dual;
FND_GLOBAL.apps_initialize ( user_id => p_user_id,
resp_id => p_resp_id,
resp_appl_id => p_resp_appl_id);
BEGIN
FOR c_po_cancel_import_rec IN c_po_cancel_import
LOOP
SELECT
distinct(type_lookup_code)
into p_look_up_code
from po_headers_all
where po_header_id = c_po_cancel_import_rec.header_id;
PO_Document_Control_PUB.control_document(
1.0,
FND_API.G_TRUE,
FND_API.G_TRUE,
x_return_status,
'PO',
p_look_up_code,
c_po_cancel_import_rec.header_id,
NULL,
NULL,
NULL,
c_po_cancel_import_rec.line_id,
NULL,
NULL,
NULL,
'CANCEL',
c_po_cancel_import_rec.cancelled_date,
NULL,
'N',
NULL,
NULL,
NULL
);
FOR i IN 1..FND_MSG_PUB.count_msg
LOOP
FND_FILE.put_line(FND_FILE.LOG,FND_MSG_PUB.Get(p_msg_index => i,p_encoded => 'F'));
END LOOP;
END LOOP;
DECLARE
l_cnt number:=0;
CURSOR c_po_valid IS
SELECT Distinct
RPAD(po_header_reference,16,' ') po_header_reference,
RPAD(ROW_STATUS,8,' ') ROW_STATUS,
RPAD(ERROR_MESSAGE,200,' ') ERROR_MESSAGE
FROM xx_tab_po_can_stg;
BEGIN
FND_FILE.PUT_LINE(FND_FILE.output,'************************|Purchase Order Cancellation Interface Report|****************************');
FND_FILE.PUT_LINE(FND_FILE.output,'*************************************************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' po_header_reference '||' '||' ROW_STATUS '||' '||' ERROR_MESSAGE');
FND_FILE.PUT_LINE(FND_FILE.output,'*************************************************************************************************');
l_cnt:=0;
FOR l_index IN c_po_valid
LOOP
FND_FILE.PUT_LINE(FND_FILE.output,' '||l_index.po_header_reference||' '||l_index.ROW_STATUS||' '||l_index.ERROR_MESSAGE);
l_cnt:=l_cnt+1;
END LOOP ; -- Cursor loop
FND_FILE.PUT_LINE(FND_FILE.output,'*************************************************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,l_cnt||' '||'Purchase Order(s) Processed..');
END;
INSERT INTO xx_tab_po_can_err(
po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,date_of_creation
,batch_id)
SELECT po_header_reference
,po_line_reference
,cancelled_date
,row_status
,error_message
,SYSDATE
,x
FROM xx_tab_po_can_stg;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
DELETE xx_tab_po_can_stg;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_can_import;
/

Note: This program only will cancel the PO Lines, order has to be cancelled manually.

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

Creation of Purchase Orders through Interface

2009-05-26T11:22:00.000-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Purchase Order Validations in the Staging Table
-- Date : 26.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE xx_po_validations(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
AS
l_count NUMBER(2);
l_subinv_name VARCHAR2(100):=NULL;
CURSOR c_po_int_valid IS
SELECT *
FROM xx_tab_po_stage
WHERE NVL(row_status,'T') IN ('E','T','R')
FOR UPDATE;
BEGIN
BEGIN
FOR c_po_int_valid_rec IN c_po_int_valid
LOOP
l_count:=0;
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_headers_all ph
,po_lines_all pl
WHERE pl.po_header_id=ph.po_header_id
AND UPPER(ph.attribute1)=UPPER(c_po_int_valid_rec.PO_HEADER_REFERENCE);
IF l_count>0 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message='Header and line combination is already existing.'
WHERE CURRENT OF c_po_int_valid;
ELSE
BEGIN
SELECT COUNT(*) INTO l_count
FROM po_vendors
WHERE UPPER(segment1)=UPPER(c_po_int_valid_rec.supplier_code)
AND end_date_active IS NULL;
IF l_count<>1 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message='Invalid supplier code.'
WHERE CURRENT OF c_po_int_valid;
ELSE
UPDATE xx_tab_po_stage
SET row_status='V'
,supplier_id=(SELECT vendor_id
FROM po_vendors
WHERE UPPER(segment1)=UPPER(c_po_int_valid_rec.supplier_code))
--AND end_date_active IS NULL)
,supplier_site_code=(SELECT max(vendor_site_code)
FROM po_vendor_sites_all
WHERE vendor_id=(SELECT vendor_id
FROM po_vendors
WHERE UPPER(segment1)=UPPER(c_po_int_valid_rec.supplier_code)
)--AND end_date_active IS NULL)
AND inactive_date IS NULL)
WHERE CURRENT OF c_po_int_valid;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
BEGIN
SELECT COUNT(*) INTO l_count
FROM org_organization_definitions
WHERE disable_date IS NULL
AND organization_id=c_po_int_valid_rec.inventory_organization_id;
IF l_count<>1 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Invalid inventory organization id.'
WHERE CURRENT OF c_po_int_valid;
ELSE
UPDATE xx_tab_po_stage
SET ship_to_location_id=(SELECT ship_to_location_id
FROM hr_locations_all
WHERE inventory_organization_id=c_po_int_valid_rec.inventory_organization_id
AND inactive_date IS NULL)
,bill_to_location_id=(SELECT location_id
FROM hr_locations_all
WHERE inventory_organization_id=c_po_int_valid_rec.inventory_organization_id
AND inactive_date IS NULL)
WHERE CURRENT OF c_po_int_valid;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Error while deriving ship to and bill to locations for the given organization id.'
WHERE CURRENT OF c_po_int_valid;
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
BEGIN
SELECT COUNT(*) INTO l_count
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id;
IF l_count=0 THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Invalid item.'
WHERE CURRENT OF c_po_int_valid;
ELSE
IF c_po_int_valid_rec.inventory_organization_id = 103 THEN
SELECT max(secondary_inventory_name)
INTO l_subinv_name
FROM mtl_secondary_inventories
WHERE organization_id=c_po_int_valid_rec.inventory_organization_id;
ELSE
SELECT max(secondary_inventory_name)
INTO l_subinv_name
FROM mtl_secondary_inventories
WHERE organization_id=c_po_int_valid_rec.inventory_organization_id
AND secondary_inventory_name NOT IN(SELECT
default_stage_subinventory
FROM wsh_shipping_parameters);
END IF;
UPDATE xx_tab_po_stage
SET item_category_id=(SELECT DISTINCT category_id FROM mtl_item_categories
WHERE inventory_item_id=(SELECT inventory_item_id
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL))
,item_description=(SELECT description
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL)
,uom_code=(SELECT primary_uom_code
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL)
,charge_account=(SELECT mp.material_account FROM MTL_PARAMETERS mp,mtl_system_items_b msi
WHERE
msi.organization_id = c_po_int_valid_rec.inventory_organization_id
AND msi.ORGANIZATION_ID = mp.ORGanization_id
AND UPPER(segment1)=c_po_int_valid_rec.item
AND msi.end_date_active IS NULL)
,item=(SELECT segment1
FROM mtl_system_items_b
WHERE UPPER(segment1)=c_po_int_valid_rec.item
AND organization_id=c_po_int_valid_rec.inventory_organization_id
AND end_date_active IS NULL)
,subinventory=l_subinv_name
WHERE CURRENT OF c_po_int_valid;
END IF;
EXCEPTION
WHEN OTHERS THEN
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Error while deriving item category,item description,uom code,item id for the given item.'
WHERE CURRENT OF c_po_int_valid;
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
END LOOP;
Update xx_tab_po_stage
set row_status = 'E'
,error_message = 'Oracle cant Create Po With 0 or -Ve Qty'
where QUANTITY <=0;
UPDATE xx_tab_po_stage
SET rate_type='Corporate'
,rate=(SELECT conversion_rate
FROM gl_daily_rates
WHERE from_currency=currency
AND to_currency='INR'
AND conversion_date=po_date
AND conversion_type='Corporate')
WHERE row_status='V'
AND currency <> 'INR';
UPDATE xx_tab_po_stage
SET error_message=NULL
WHERE row_status='V';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
--Validation for Daily Rates in the General Ledger
--Upadate by Krishna on Date 15-05-2007
DECLARE
CURSOR c_rate_verify IS
SELECT *
FROM xx_tab_po_stage
Where
currency not in 'INR'
and row_status = 'V'
FOR UPDATE;
BEGIN
FOR c_rate_verify_rec IN c_rate_verify
LOOP
IF c_rate_verify_rec.rate is null then
UPDATE xx_tab_po_stage
SET row_status='E'
,error_message=error_message||' '||'Daily Rate in the GL was not Defined.'
WHERE CURRENT OF c_rate_verify;
END IF;
END LOOP;
END;
DECLARE
x NUMBER :=NULL;
CURSOR c IS
SELECT DISTINCT PO_HEADER_REFERENCE
FROM xx_tab_po_stage
WHERE row_status='V';
BEGIN
FOR c_rec IN c
LOOP
select meai_po_seq3.nextval into x from dual;
UPDATE xx_tab_po_stage
SET interface_header_id=x
WHERE UPPER(PO_HEADER_REFERENCE )=UPPER(c_rec.PO_HEADER_REFERENCE)
AND row_status='V';
END LOOP;
END;
commit;
DECLARE
x NUMBER;
y NUMBER;
Z NUMBER;
CURSOR c IS
SELECT DISTINCT interface_header_id
FROM xx_tab_po_stage;
BEGIN
FOR c_rec IN c
LOOP
x:=c_rec.interface_header_id;
DECLARE
CURSOR c1 IS
SELECT DISTINCT PCS_pono
FROM xx_tab_po_stage
WHERE interface_header_id=x
AND row_status='V';
BEGIN
y:=1;
FOR c1_rec IN c1
LOOP
SELECT meai_po_seq1.NEXTVAL INTO Z
FROM dual;
UPDATE xx_tab_po_stage
SET interface_line_id=Z
,lineno=y
WHERE interface_header_id=x
AND UPPER(PCS_pono)=UPPER(c1_rec.PCS_pono);
y:=y+1;
--Z:=Z+1;
commit;
END LOOP;
END;
END LOOP;
END;
UPDATE xx_tab_po_stage
SET ROW_STATUS = 'E'
,ERROR_MESSAGE = error_message||'Sub inventory Not Picked for Distributions.'
where subinventory is null
and INVENTORY_ORGANIZATION_ID <> 138;
commit;
DECLARE
l_count1 NUMBER:=0;
CURSOR c_verify IS
SELECT DISTINCT PO_HEADER_REFERENCE
FROM xx_tab_po_stage;
BEGIN
FOR c_verify_rec IN c_verify
LOOP
SELECT COUNT(*) INTO l_count1
FROM xx_tab_po_stage
WHERE row_status='E'
AND UPPER(PO_HEADER_REFERENCE)=UPPER(c_verify_rec.PO_HEADER_REFERENCE);
IF l_count1>0 THEN
UPDATE xx_tab_po_stage
SET row_status='R'
,error_message=error_message||' '||'Some of the lines under this slipno are errored out.'
WHERE UPPER(PO_HEADER_REFERENCE)=UPPER(c_verify_rec.PO_HEADER_REFERENCE)
AND row_status='V';
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END;
DELETE po_lines_interface where INTERFACE_HEADER_ID in (select interface_header_id from po_headers_interface
where process_code = 'ACCEPTED');
DELETE po_distributions_interface where INTERFACE_HEADER_ID in (select interface_header_id from po_headers_interface
where process_code = 'ACCEPTED');
DELETE po_headers_interface where upper(process_code) = 'ACCEPTED';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM());
END xx_po_validations;
/

-- ---------------------------------------------------------
-- Exporting Valid Purchase Order to Interface Tables
-- ---------------------------------------------------------

CREATE OR REPLACE PROCEDURE xx_po_import(errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
l_request number;
l_seq NUMBER:=0;
l_rstatus varchar2(1):=null;
l_errm varchar2(1000):=null;
l_slip number:=null;
l_po number:=null;
l_cnt number:=null;
CURSOR c_po_valid IS
SELECT Distinct
PO_HEADER_REFERENCE,
ROW_STATUS,
ERROR_MESSAGE
FROM xx_tab_po_stage
WHERE row_status IN ('E','T');

BEGIN
/* Inserting into Headers Interface for INR */

INSERT INTO po_headers_interface(
document_type_code
,creation_date
,vendor_id
,vendor_site_code
,ship_to_location_id
,bill_to_location_id
,currency_code
,agent_id
,attribute1
,interface_header_id
,vendor_doc_num
,action
--,rate
--,rate_date
,rate_type
,budget_account_id
,created_by
,last_update_login
,last_update_date
,last_updated_by)
SELECT DISTINCT
'STANDARD'
,po_date
,supplier_id
,supplier_site_code
,ship_to_location_id
,bill_to_location_id
,currency
,69
,PO_HEADER_REFERENCE
,interface_header_id
,PO_HEADER_REFERENCE
,'ORIGINAL'
--,rate
--,po_date
,rate_type
,1002
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,sysdate
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V'
AND currency IN (SELECT DISTINCT(CURRENCY_CODE) FROM FND_CURRENCIES)
AND currency = 'INR';

/* Inserting into Headers Interface for other Currencies */

INSERT INTO po_headers_interface(
document_type_code
,creation_date
,vendor_id
,vendor_site_code
,ship_to_location_id
,bill_to_location_id
,currency_code
,agent_id
,attribute1
,interface_header_id
,vendor_doc_num
,action
--,rate
,rate_date
,rate_type
,budget_account_id
,created_by
,last_update_login
,last_update_date
,last_updated_by)
SELECT DISTINCT
'STANDARD'
,po_date
,supplier_id
,supplier_site_code
,ship_to_location_id
,bill_to_location_id
,currency
,69
,PO_HEADER_REFERENCE
,interface_header_id
,PO_HEADER_REFERENCE
,'ORIGINAL'
--,rate
,po_date
,rate_type
,1002
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,sysdate
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V'
AND currency IN (SELECT DISTINCT(CURRENCY_CODE) FROM FND_CURRENCIES)
AND currency <> 'INR';

/* Inserting into Lines Interface */

INSERT INTO po_lines_interface(
line_num
,line_type_id
,item
,category_id
,item_description
,uom_code
,quantity
,unit_price
,line_attribute1
,shipment_num
,ship_to_organization_id
,ship_to_location_id
,interface_header_id
,interface_line_id
,action
,promised_date
,receiving_routing_id
,created_by
,last_update_login
,creation_date
,last_update_date
,last_updated_by)
SELECT DISTINCT
lineno
,1
,item
,item_category_id
,item_description
,uom_code
,quantity
,price
,PCS_pono
,1
,inventory_organization_id
,ship_to_location_id
,interface_header_id
,interface_line_id
,'ORIGINAL'
,promised_date
,3
,FND_GLOBAL.user_id
,FND_GLOBAL.login_id
,po_date
,sysdate
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V';

/* Inserting into PO Distributions Interface */

INSERT INTO po_distributions_interface(
quantity_ordered
,distribution_num
,destination_type_code
,charge_account_id
,interface_header_id
,interface_line_id
,interface_distribution_id
,destination_organization_id
,destination_subinventory
,budget_account_id
,created_by
,creation_date
,last_update_date
,last_update_login
,last_updated_by)
SELECT DISTINCT
quantity
,1
,'INVENTORY'
,charge_account
,interface_header_id
,interface_line_id
,interface_line_id
,inventory_organization_id
,subinventory
,1002
,FND_GLOBAL.user_id
,po_date
,sysdate
,FND_GLOBAL.login_id
,FND_GLOBAL.user_id
FROM xx_tab_po_stage
WHERE row_status='V';
FND_FILE.PUT_LINE(FND_FILE.output,'User: '||FND_GLOBAL.USER_NAME||' '||' Program Time: '||to_char(sysdate,'DD-MON-YYYY HH:MI:SS AM'));
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,'*********************************************');
FND_FILE.PUT_LINE(FND_FILE.output,'**********************|Purchase Order Interface Report|***********************');
FND_FILE.PUT_LINE(FND_FILE.output,'********************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' PO_HEADER_REFERENCE '||' '||'ROW_STATUS '||' '||' ERROR_MESSAGE');
FND_FILE.PUT_LINE(FND_FILE.output,'***********************************************************************');
l_cnt:=0;
FOR l_index IN c_po_valid
LOOP
FND_FILE.PUT_LINE(FND_FILE.output,' '||l_index.PO_HEADER_REFERENCE||' '||l_index.ROW_STATUS||' '||l_index.ERROR_MESSAGE);
l_cnt:=l_cnt+1;
END LOOP ; -- Cursor loop
FND_FILE.PUT_LINE(FND_FILE.output,'***********************************************************************');
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,' '||l_cnt||' '||'Purchase Order(s) Processed..');
FND_FILE.PUT_LINE(FND_FILE.output,' ');
FND_FILE.PUT_LINE(FND_FILE.output,'*****************|End-Of-Report|*******************************');
FND_FILE.PUT_LINE(FND_FILE.output,' ');

/* Inserting Errored records into error table */

INSERT INTO xx_tab_po_err
(
SUPPLIER_CODE
,PROMISED_DATE
,CURRENCY
,PO_HEADER_REFERENCE
,ITEM
,QUANTITY
,PRICE
,PCS_PONO
,INVENTORY_ORGANIZATION_ID
,ROW_STATUS
,ERROR_MESSAGE
,DATE_OF_CREATION
,BATCH_ID
,PO_DATE
)
SELECT SUPPLIER_CODE
,PROMISED_DATE
,CURRENCY
,PO_HEADER_REFERENCE
,ITEM
,QUANTITY
,PRICE
,PCS_PONO
,INVENTORY_ORGANIZATION_ID
,ROW_STATUS
,ERROR_MESSAGE
,SYSDATE
,xx_po_seq.NEXTVAL
,PO_DATE
FROM xx_tab_po_stage
where row_staus = 'E';
--l_request:=fnd_request.submit_request('PO','POXPOPDOI', null, null, FALSE,null,'STANDARD',null,'Y',null,'APPROVED',null,null,null,null);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Purchasing Documents Import Request ID :'||l_request);
DELETE xx_tab_po_stage where row_status not in ('T','E','R');
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
END xx_po_import;
/

-- ----------------------------------------------------------------------------------
-- Running the Purchase order Import
-- ---------------------------------------------------------------------------------

Login to Applications

PO User --> Submit request --> Import Standar Purchase orders

Now all the orders will be created which are loaded in the interface.

Note: In po_headers_interface the process_code will be 'ACCEPTED' for the orders which are created, The process_code will be 'REJECTED' if it errored out.

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

Getting the Source Code from database using Query

2009-05-22T11:57:00.001-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Get the Source Code from database without junck characters using Query
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

SELECT
decode(rownum ,1,'CREATE OR REPLACE '||rTRIM(rTRIM(t.text,CHR(10))),rTRIM(rTRIM(t.text,CHR(10)))) text
from dba_source t
Where type = 'PACKAGE BODY'
and name = &Package_name

-- ---------------------------------------------------------------------------------
-- Purpose : Anonoumos block to Get the Source Code from database
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ----------------------------------------------------------------------------------

DECLARE
x varchar2(200);
n number;
cursor c_rec is
select text,line,name,type from all_source
where name = &Package_name
and type like 'PACKAGE';
begin
dbms_output.put_line('================================================ ');
dbms_output.put_line('============ Package Specifications============= ');
dbms_output.put_line('================================================ ');
dbms_output.put_line(' ');
for c in c_rec
loop
n:= c.line;
select rtrim(ltrim(text,chr(10)),chr(10))
into x
from all_source
where name = c.name
and type = c.type
--and type <> 'PACKAGE'
and line = c.line;
dbms_output.put_line(x);
end loop;
exception
when others then
dbms_output.put_line('Error at '|| n || ' '|| 'line, ' || sqlerrm);
end;

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

Concurrent requests Errors report for the week

2009-05-22T10:32:00.000-07:00

-- -------------------------------------------------------------------------------------------------------------
-- Purpose : Get the detials of errors and warnings for the concurrent requests during the week.
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- -------------------------------------------------------------------------------------------------------------

SELECT
fcr.request_id,
fcp.user_concurrent_program_name Conc_prog_name,
fcp.CONCURRENT_PROGRAM_NAME Short_name,
fe.EXECUTION_FILE_NAME execution_file,
(select meaning from fnd_lookups
where lookup_type = 'CP_EXECUTION_METHOD_CODE'
and lookup_code = fe.EXECUTION_METHOD_CODE) Execution_Method,
fa.BASEPATH Application_Top,
fr.RESPONSIBILITY_KEY Responsibility,
(select meaning from fnd_lookups
where lookup_type = 'CP_PHASE_CODE'
and lookup_code = fcr.PHASE_CODE) Phase_Code,
(select meaning from fnd_lookups
where lookup_type = 'CP_STATUS_CODE'
and lookup_code = fcr.STATUS_CODE) Status_code,
fu.USER_NAME Requested_By
from fnd_concurrent_requests fcr,
fnd_user fu,
fnd_concurrent_programs_vl fcp,
fnd_responsibility fr,
fnd_executables fe,
FND_APPLICATION fa
where fcr.REQUESTED_BY = fu.USER_ID
and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
and fr.RESPONSIBILITY_ID = fcr.RESPONSIBILITY_ID
and fcp.executable_ID = fe.executable_ID
and fa.APPLICATION_ID = fe.APPLICATION_ID
and fcr.STATUS_CODE in('E','X','G','D')
and fcr.ACTUAL_COMPLETION_DATE > sysdate-7
order by 9;

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

To Get the Sales order, Line, Delivery and invoice Status

2009-05-22T10:10:00.000-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Query to retrive the Sales order,Line, Delivery and invoice Status.
-- Date : 22.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

SELECT
ooh.ORDER_NUMBER So_Num,
ooh.HEADER_ID,
ool.ordered_quantity ordered_qty,
(select sum(a.SHIPPED_QUANTITY) from wsh_delivery_details a
where SOURCE_HEADER_ID =ooh.HEADER_ID) shipped_qty,
wnd.DELIVERY_ID,
wnd.NAME delivery_name,
(select distinct batch_id from wsh_delivery_details
where SOURCE_HEADER_ID =ooh.HEADER_ID) batch_id,
initcap(ooh.FLOW_STATUS_CODE) Order_Status,
initcap(ool.FLOW_STATUS_CODE) Line_status,
decode(wnd.STATUS_CODE,'CL','Closed','Open') Delivery_status,
(select decode(count(*),0,'Invoice Not Created','Invoice Generated') from ra_customer_trx_lines_all rctl
where rctl.line_type = 'LINE'
and rctl.interface_line_attribute1 = to_char(ooh.order_number)) inv_Status
from
oe_order_headers_all ooh,
wsh_new_deliveries wnd,
oe_order_lines_all ool
where ooh.HEADER_ID = ool.HEADER_ID
and wnd.SOURCE_HEADER_ID = ooh.HEADER_ID
and ooh.order_number = :order_num;

--=======================================================
--- Query to retrive the Sales order and associate invoice.
--=======================================================

SELECT
ooh.order_number,
ooh.ORDERED_DATE,
ooh.FLOW_STATUS_CODE SO_Status,
ool.line_number,
msi.SEGMENT1 Item_Name,
ool.ordered_quantity,
rct.TRX_NUMBER Invoice_Num,
rct.TRX_date Invoice_Date,
rct.STATUS_TRX,
decode(rct.COMPLETE_FLAG,'Y','Completed','In Complete') Inv_Status,
ool.UNIT_SELLING_price*ool.ordered_quantity line_total
from
oe_order_headers_all ooh,
oe_order_lines_all ool,
hz_cust_accounts hca,
ra_customer_trx_lines_all rctl,
ra_customer_trx_all rct,
mtl_system_items msi
where
ooh.header_id=ool.header_id
and ooh.sold_to_org_id=hca.cust_account_id
and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID
and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID
and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID
and rctl.LINE_TYPE = 'LINE'
and rctl.interface_line_attribute1 = to_char(ooh.ORDER_NUMBER)
and rctl.QUANTITY_invoiced = ool.ORDERED_QUANTITY
and ooh.order_number= 1195372 --517980
order by ool.line_number;

Note: This Query is based on one Invoice for every sales order, and invoice will have those many lines as in the salesorder.

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

Getting the concurrent output into Excel Sheet

2009-05-20T10:43:00.000-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Steps to get the concurrent output into Excel Sheet
-- Date : 20.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

1. To get the output in the excel format from concurrent output we need to modify any Viewer Options like (PCL).

Navigation path System Administrator à Install à Viewer Options

2. The below is the update statement which will change the PCL viewer
option to excel. After this updates the concurrent programs whichever is having
output type as PCL will open directly in ExcelSheet.

Statement: update fnd_mime_types_tl

set mime_type = 'application/vnd.ms-excel'

,description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'

,last_update_date = sysdate

where file_format_code = 'PCL'

and mime_type = 'application/vnd.hp-PCL';

commit;

Viewer Options Screen.

3. Run the concurrent program which is having the output type as PCL to test the excel output.

==============================================

Resetting the Workflow for Purchase Order

2009-05-20T09:01:00.000-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Steps to restart/reset the Purchase order workflow.
-- Date : 20.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

If any Standard Purchase Order got strucked in the Workflow if we need to reset it then follow the process below.

Setps:

1. SELECT ph.segment1 PO_Num,
ph.ORG_ID,
ph.TYPE_LOOKUP_CODE PO_TYPE,
ph.authorization_status,
ph.WF_ITEM_KEY,
ph.WF_ITEM_TYPE
from po_headers_all ph
where ph.segment1 = '225343' -- PO Number
and ph.TYPE_LOOKUP_CODE = 'STANDARD';

2. Download the file poxrespo.sql and run the script in the instance where the purchase order got strucked.

3. The workflow will be resetted after the script execution and user can try re-approving the order now.

Suggestion: Make sure that workflow background process is running and scheduled.

If you have any doubts follow the Oracle note.id: 390023.1

=====================================================

Resolving the exception(WSH_CHANGE_SCHED_DATE) raised

2009-05-19T07:10:00.000-07:00

-- ---------------------------------------------------------------------------------
-- Purpose : Resolving the exception(WSH_CHANGE_SCHED_DATE) raised
-- Date : 19.May.2009
-- Created by : Chaitanya
-- .....
-- ---------------------------------------------------------------------------------

Possibilities of the error: If an order has the scheduled shipment date greater than the actual shipping date.

Please follow the steps below.

1. If you want to run like that you may need to disable that exception once after that continue to second step.
2. Loging to OM Super User --> Shipping --> Exceptions and query for WSH_CHANGE_SCHED_DATE.
3. Tools --> Resolve/Purge Exceptions
4. Then enter the exception name which you got, this will purge the exceptions in the interface.
5. Once all the steps are complete you can proceed with normal Shipping process.

Follow the document WSHEXCEP.doc which will give more information with screenshots.

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

Restarting the Sales Order Workflow

2009-05-18T09:17:00.000-07:00

-- ------------------------------------------------------------------------------
-- Purpose : Restarting the Sales Order Workflow
-- Date : 18.May.2009
-- Created by : Chaitanya
-- .....
-- ------------------------------------------------------------------------------

When ever the Sales order got strucked at the shipment with status Interfaced and Sales order status as Fulfilled.
Please follow the steps given below.

1. Query for the Order which is having the status as Fulfilled.
2. Go to lines and do right click and select retry activities in the error.
3. Then it will show the activity pending list choose the invoice interface.
4. Invoice will start processing after that.

Follow the document SOWFREL.doc which will give more information with screenshots.

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

Query to Get the Trace file location of a concurrent request

2009-05-17T07:02:00.001-07:00

-- ------------------------------------------------------------------------------
-- Purpose : Query to Get the Trace file location of a concurrent request
-- Date : 17.May.2009
-- Created by : Chaitanya
-- .....
-- ------------------------------------------------------------------------------

SELECT req.request_id ,
'Trace id: '||oracle_Process_id,
'Trace Flag: '||req.enable_trace,
'Trace Name:
'||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
'Prog. Name: '||prog.user_concurrent_program_name,
'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
'Status : '||decode(phase_code,'R','Running')
||'-'||decode(status_code,'R','Normal'),
'SID Serial: '||ses.sid||','|| ses.serial#,
'Module : '||ses.module
from fnd_concurrent_requests req, v$session ses, v$process proc,
v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
fnd_executables execname
where
1=1
and req.request_id = :request_id
and req.oracle_process_id=proc.spid(+)
and proc.addr = ses.paddr(+)
and dest.name='user_dump_dest'
and dbnm.name='db_name'
and req.concurrent_program_id = prog.concurrent_program_id
and req.program_application_id = prog.application_id
and prog.application_id = execname.application_id
and prog.executable_id=execname.executable_id;

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

Query to retrive Account Receivable Invoice, Receipt and Payment Status

2009-05-17T06:56:00.000-07:00

-- ------------------------------------------------------------------------------------------------------
-- Purpose : Query to retrive Account Receivable Invoice, Receipt and Payment Status
-- Date : 17.May.2009
-- Created by : Chaitanya
-- .....
-- ------------------------------------------------------------------------------------------------------

SELECT ct.CUSTOMER_TRX_ID,
ps_inv.TRX_NUMBER,
ct.ct_reference,
(select sum(extenDed_amount) from ra_customer_trx_lines_all
where customer_trx_id = ct.CUSTOMER_TRX_ID) Invoice_Amount,
cr.RECEIPT_NUMBER,
cr.STATUS,
cr.AMOUNT total_Receipt_amount,
nvl((select sum(-amount)
from ar_adjustments_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0) Adjustment_Amount
/*decode((nvl((select sum(-amount)
from ar_adjustments_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0)+cr.AMOUNT) -
(select sum(extenDed_amount) from ra_customer_trx_lines_all
where customer_trx_id = ct.CUSTOMER_TRX_ID),0,'Invoice Fully Paid', 'Partially Paid') Payment_Status */
FROM ar_receivable_applications_all app,
ar_cash_receipts cr,
ar_payment_schedules_all ps_inv,
ra_customer_trx_all ct,
ar_receivables_trx_all art
WHERE 1=1
AND app.cash_receipt_id = cr.cash_receipt_id
AND ct.customer_trx_id(+) = ps_inv.customer_trx_id
AND app.applied_payment_schedule_id = ps_inv.payment_schedule_id
AND art.receivables_trx_id(+) = app.receivables_trx_id
--AND cr.RECEIPT_NUMBER = :ar_receipt_num
AND ct.TRX_NUMBER = :ar_invoice_num;

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