Wednesday 17 December 2014

ora-22290: operation would exceed the maximum number of opened files or lobs

The clob file's are opened .
use the following statement

DBMS_LOB.filecloseall;

Tuesday 16 December 2014

How to insert report output file into a table .

CREATE OR REPLACE PROCEDURE apps.xx_report_prc (
x_errbuf OUT VARCHAR2,
x_retcode OUT NUMBER,
p_org_id IN NUMBER,
p_report IN VARCHAR2,
p_from_xx_no IN VARCHAR2,
p_to_xx_no IN VARCHAR2
)
AS
l_resp_appl_id fnd_application.application_id%TYPE:= fnd_global.resp_appl_id;
l_resp_id fnd_responsibility.responsibility_id%TYPE:= fnd_global.resp_id;
l_user_id fnd_user.user_id%TYPE := fnd_global.user_id;
v_request_id VARCHAR2 (100);
p_return_code NUMBER;
p_return_msg VARCHAR2 (100);
p_report_type VARCHAR2 (100);
l_count NUMBER := 0;
l_status VARCHAR2 (10);
v_file_loc BFILE;
v_pdf_file_loc BLOB;
v_pdf_file_size INTEGER;
l_file VARCHAR2 (240);
------------------------------------------------
l_set_layout BOOLEAN;
l_messase VARCHAR2 (240);
l_request_id NUMBER;
l_phase VARCHAR2 (100);
l_status1 VARCHAR2 (100);
l_dev_phase VARCHAR2 (100);
l_dev_status VARCHAR2 (100);
l_wait_for_request BOOLEAN := FALSE;
l_get_request_status BOOLEAN := FALSE;
output_layout_failed EXCEPTION;
request_submission_failed EXCEPTION;
request_completion_abnormal EXCEPTION;
l_phase_code VARCHAR2 (12);

CURSOR c1
IS
SELECT h.*
FROM xx_custom_table h;
BEGIN
FOR i IN c1
LOOP
l_count := l_count + 1;
DBMS_OUTPUT.put_line ('count' || l_count);
fnd_global.apps_initialize (l_user_id, l_resp_id, l_resp_appl_id);
COMMIT;

BEGIN
l_set_layout :=
fnd_request.add_layout
(template_appl_name => 'XX',
template_code => 'XXSTER', --Data Template Code
template_language => 'en',
template_territory => 'US',
output_format => 'PDF'
);

IF l_set_layout
THEN
v_request_id :=
fnd_request.submit_request
(application => 'XX',
program => 'XXSTER', ---Concurrent program Short Name
description => 'XX Output Report',
start_time => SYSDATE,
sub_request => NULL, -- Pass the Concurrent Program Parameters
argument1 => i.organization_id, -- Organization
argument2 => p_report_type, -- REPORT TYPE
argument3 => i.xx_ref_no, -- Document No fROM.
argument4 => i.xx_ref_no, -- Document No To.
argument5 => NULL, -- Email ID
argument6 => 'FTP', -- Report Submitted From
argument7 => NULL,
argument8 => NULL,
argument9 => NULL,
argument10 => NULL,
argument11 => NULL
);
p_return_msg := 'Request submitted.ID=' || v_request_id;
fnd_file.put_line (fnd_file.LOG, 'request_id' || p_return_msg);
DBMS_OUTPUT.put_line ('Request Id ' || p_return_msg);
p_return_code := 0;
DBMS_OUTPUT.put_line ('RETURN CODE' || p_return_code);
COMMIT;

IF v_request_id > 0
THEN
DBMS_OUTPUT.put_line ('request_id >0');

LOOP
l_wait_for_request :=
fnd_concurrent.wait_for_request
(request_id => v_request_id,
INTERVAL => 5,
max_wait => 0,
phase => l_phase,
status => l_status,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
MESSAGE => l_messase
);

BEGIN
SELECT phase_code
INTO l_phase_code
FROM fnd_concurrent_requests
WHERE request_id = v_request_id;
EXCEPTION
WHEN OTHERS
THEN
l_phase_code := 'X';
END;

EXIT WHEN ( l_phase_code = 'C'
OR l_phase_code = 'X'
OR l_phase_code = 'I'
);
END LOOP;

BEGIN
SELECT 'X'
INTO l_status
FROM fnd_concurrent_requests
WHERE request_id = v_request_id
AND phase_code = 'C'
AND status_code = 'C';
EXCEPTION
WHEN OTHERS
THEN
l_status := NULL;
END;

DBMS_OUTPUT.put_line ('l_status ' || l_status);

IF l_status = 'X'
THEN
DBMS_OUTPUT.put_line ('file inseration ');

BEGIN
SELECT SUBSTR (file_name, INSTR (file_name, '/', -1) + 1)
INTO l_file
FROM apps.fnd_conc_req_outputs
WHERE concurrent_request_id = v_request_id;
EXCEPTION
WHEN OTHERS
THEN
l_file := NULL;
END;

BEGIN
v_file_loc := BFILENAME ('XX_OUTPUT_FILE', l_file);---- XX_OUTPUT_FILE is directory and l_file is file name
DBMS_LOB.fileopen (v_file_loc, DBMS_LOB.file_readonly);-- it is mandatory it is abled to opened the file
v_pdf_file_size := DBMS_LOB.getlength (v_file_loc);
DBMS_OUTPUT.put_line ('PDF file size: '
|| v_pdf_file_size
); --v_diagram_size
DBMS_OUTPUT.put_line ('Inserting PDF file size Row');

INSERT INTO xx_table
(request_id, pdf_out_put_file,
file_name,
organization_id, file_type,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, status
)
VALUES (v_request_id, EMPTY_BLOB,
i.xx_ref_no
|| '_'
|| (TO_CHAR (SYSDATE,
'DD-MON-YYYY_hh24_mi_ss'
)
)
|| '.pdf',
i.organization_id, p_report_type,
SYSDATE, fnd_global.user_id,
SYSDATE, fnd_global.user_id,
fnd_global.user_id, 'N'
)
RETURNING pdf_out_put_file
INTO v_pdf_file_loc;

DBMS_OUTPUT.put_line ('Loading PDF From File');
DBMS_LOB.loadfromfile (v_pdf_file_loc,
v_file_loc,
v_pdf_file_size
);
COMMIT;
DBMS_LOB.filecloseall;---it is not mandatory if you are not use the command the lob file is not closed it is able to processed upto 10 records only

UPDATE xx_custom_table xec
SET attribute2 = 'Y'
WHERE xx_ref_no = i.xx_ref_no;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'insert stmt Exception '
|| SQLCODE
|| SQLERRM
);
DBMS_OUTPUT.put_line ( 'OTHERS Exception '
|| SQLCODE
|| SQLERRM
);
END;
ELSE
RAISE request_completion_abnormal;
END IF;
ELSE
RAISE request_submission_failed;
END IF;
ELSE
RAISE output_layout_failed;
END IF;
EXCEPTION
WHEN output_layout_failed
THEN
fnd_file.put_line (fnd_file.LOG, 'Out put Layout failed');
WHEN request_submission_failed
THEN
fnd_file.put_line (fnd_file.LOG,
'Concurrent request submission failed'
);
WHEN request_completion_abnormal
THEN
fnd_file.put_line (fnd_file.LOG,
'Submitted request completed with error'
|| l_request_id
);
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'ERROR:' || SUBSTR (SQLERRM, 0, 240)
);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
p_return_msg :=
'REQUEST SET SUBMISSION FAILED -UNKNOWN ERROR:' || SQLERRM;
p_return_code := 2;
fnd_file.put_line (fnd_file.LOG, 'Exception error msg' || p_return_msg);
fnd_file.put_line (fnd_file.LOG, 'return code' || p_return_code);
END;

Tuesday 2 December 2014

Rows to columns conversion:

(SELECT LIST.tm

FROM (WITH rec AS

(SELECT '1,2,3,4,5' tm

FROM DUAL)

SELECT REGEXP_SUBSTR (tm, '[^,]+', 1, LEVEL) tm

FROM rec

CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (tm, ',', NULL))

FROM rec)) LIST

WHERE LIST.tm IS NOT NULL)

Union and union all:

(SELECT LIST.tm

FROM (WITH rec AS

(SELECT '1,2,3,4,5' tm

FROM DUAL)

SELECT REGEXP_SUBSTR (tm, '[^,]+', 1, LEVEL) tm

FROM rec

CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (tm, ',', NULL))

FROM rec)) LIST

WHERE LIST.tm IS NOT NULL)

union all

(SELECT LIST.tm

FROM (WITH rec AS

(SELECT '1,2,3,4,5,6' tm

FROM DUAL)

SELECT REGEXP_SUBSTR (tm, '[^,]+', 1, LEVEL) tm

FROM rec

CONNECT BY LEVEL <= (SELECT LENGTH (REPLACE (tm, ',', NULL))

FROM rec)) LIST

WHERE LIST.tm IS NOT NULL)

Monday 10 November 2014

Difference between Process and Discrete Manufacturing.

Sunday 9 November 2014

REVERSE FOR LOOP

5 4 3 2 1

4 3 2 1

3 2 1

2 1

DECLARE

var VARCHAR2 (20);

BEGIN

FOR i IN REVERSE 1 .. 5

LOOP

FOR j IN REVERSE 1 .. i

LOOP

var := var || ' ' || j;

END LOOP;

DBMS_OUTPUT.put_line (var);

var := NULL;

END LOOP;

END;

Wednesday 29 October 2014

Difference between Value sets and Lookups

Tuesday 28 October 2014

Form Personalization in Batch Details form

Req 1:
Default batch number as lot number (Non FG Batch)and WIP Qty as Transaction Qty for WIP batches in Pending lot screen (Batch Details form):

Condition:

:GME_BATCH_HEADER.BATCH_STATUS IN (1,2)
AND :GME_PRODUCT_LOTS.LOT_NUMBER IS NULL
AND :GME_MTL_DTL_PROD.LINE_TYPE = 1
AND EXISTS (SELECT distinct INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B
WHERE ORGANIZATION_ID in (101,102) AND INVENTORY_ITEM_ID = :GME_PRODUCT_LOTS.INVENTORY_ITEM_ID AND ITEM_TYPE <> 'FG')

Value: ${item.gme_batch_header.batch_no.value}

Req 2:Change EXPIRATION_DATE Format(Pending Lot Form)

DD:MON:YYYY to DD:MON:YYYY HH24:MI:SS

Value: DD-MON-YYYY HH24:MI:SS

Req 3:If Revision number is null then return error msg (Batch Details Form Product tab)

Condition::GME_MTL_DTL_PROD.REVISION is null and fnd_profile.value('MFG_ORGANIZATION_ID')=(select ORGANIZATION_ID from org_organization_definitions where ORGANIZATION_CODE ='XYZ')

Friday 17 October 2014

How to create FND Messages in oracle apps ?

Application Adiministration --> Application --> Messages

select * from FND_NEW_MESSAGES where MESSAGE_NAME like'XHL_INV_RESTRICT_STATUS'

Migrate from One instance to another instance:

Download:

FNDLOAD apps/$1 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XHL_INV_RESTRICT_STATUS_MES.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XX" MESSAGE_NAME="XHL_INV_RESTRICT_STATUS"

upload:

FNDLOAD apps/$1 O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XHL_INV_RESTRICT_STATUS_MES.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Tuesday 14 October 2014

Transaction Types in Inventory

SELECT DISTINCT mmt.transaction_action_id, mtt.transaction_source_type_id,

mtt.transaction_type_id, mtt.transaction_type_name

FROM mtl_transaction_types mtt, mtl_material_transactions mmt

WHERE 1 = 1 AND mtt.transaction_type_id = mmt.transaction_type_id

TRANSACTION_ACTION_ID	TRANSACTION_SOURCE_TYPE_ID	TRANSACTION_TYPE_ID	TRANSACTION_TYPE_NAME
31	5	44	WIP Completion
1	5	35	WIP Issue
27	5	43	WIP Return
32	5	17	WIP Completion Return
27	13	42	Miscellaneous receipt
1	13	32	Miscellaneous issue
1	6	31	Account alias issue
27	3	40	Account receipt
1	3	1	Account issue
2	4	64	Move Order Transfer
27	6	41	Account alias receipt
29	1	71	PO Rcpt Adjust
52	13	89	Container Split
2	13	2	Subinventory Transfer
1	1	36	Return to Vendor
28	2	52	Sales Order Pick
8	10	8	Physical Inv Adjust
2	13	51	Backflush Transfer
27	12	15	RMA Receipt
27	1	18	PO Receipt
51	13	88	Container Unpack
1	2	33	Sales order issue
4	9	4	Cycle Count Adjust
36	2	10,008	COGS Recognition
50	13	87	Container Pack

Tuesday 2 September 2014

ORA-01400: cannot insert NULL into ("INV"."MTL_MATERIAL_STATUS_HISTORY"."UPDATE_METHOD") in Package INV_MATERIAL_STATUS_PKG Procedure Insert_Status_history

When you encounter an ORA-01400 error, the following error message will appear:

ORA-01400: cannot insert NULL into ("SCHEMA"."TABLE_NAME"."COLUMN_NAME")

--> In this scenario ("INV"."MTL_MATERIAL_STATUS_HISTORY"."UPDATE_METHOD")
we need to pass UPDATE_METHOD :=2;

l_status_rec.UPDATE_METHOD :=2;

Thursday 21 August 2014

How to convert varchar2/Number to Hexadecimal ?

Using RAWTOHEX Function .

select RAWTOHEX('a') from dual

Wednesday 20 August 2014

how many maximum parameters in concurrent program

100 parameters

Thursday 7 August 2014

REP-0069 :Internal Error REP -57054: In -process job terminated :finished sucessfully but output is voided

Hi,

Since this report got an internal error (Finished successfully but output is voided), it was completed with error for the request id 12345

I think it happens intermittently because of cache of oracle reports temporary files.

We can find the solution for this error in the following metalinks.

To resolve this issue, we need to add the below property names and values in the reports server configuration file rwbuilder.conf

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=1137914333622456&id=1351791.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=bccvmcua8_139

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=1138133219561898&id=1237834.1&_afrWindowMode=0&_adf.ctrl-state=bccvmcua8_202

http://erdemcer.blogspot.in/2014/05/oracle-reports-rep-0069-rep-57054-error.html

Thursday 24 July 2014

Creating Formula in Oracle Process Manufacturing(OPM)

http://dj-oracleapps.blogspot.in/2014/02/creating-formula-in-oracle-process.html

Wednesday 23 July 2014

Write a query for to disply 1st day of the last month in sql

select trunc(trunc(to_date('12-apr-2013','dd-mon-yyyy'),'mm')-1,'mm') from dual;

Tuesday 22 July 2014

How to create Profile ?

Profile Option in Oracle Apps:

Profile Option values control the behavior of Oracle Apps, in other words they determine how Oracle Apps should run. The value for a profile option can be changed any time.

For Example we have a profile option called MO: Operating Unit. Assigning a value to this profile option will determine what operating unit it should use when a user gets into a particular responsibility.

We have two types of Profile options – System and Personal profile options depending on to whom they are visible and who can update their values.

System Profile options are visible and can be updated only in System Administrator responsibility. In short they will be maintained by a System Administrator only.

User Profile Options are visible and can be updated by any end user of Oracle Apps.

Profile Options can be set at different levels. Site level being the highest and User being the lowest in the heirarchy. If a profile option is assigned at two levels, then value assigned at lowest level takes the precedence.

Site (restricted to the whole of Apps)
Application ( restricted only to a particular application like Payables, Receivables)
Responsibility (restricted only to a particular responsibility)
Organization (restricted to a particular organization)
User (restricted to a user)

Now let’s see how to create a new profile option and assign a value to it.
Say you want to create a profile option called erpSchools Debug and the possible values that the user can choose are Yes, No. If the user chooses Yes, then the custom concurrent program will print all the debug messages otherwise the debug messages will not printed in the log file of the concurrent request.

Creating a Profile Option:
Navigation: Application Developer/system administration > Profile

Profiles window will open up where you enter the below details:
Name: Give a meaningful and unique name for this profile option

Application: Enter the application for which this profile option will be used like Payables, Receivables etc. If it is for a custom program then choose the custom application.

User Profile Name: Again enter a meaningful and unique name. This is the name that the users will see in the Profile Options window.

Description: Enter the purpose or usage of this option.

Heirarchy Type Access Levels: At what levels this profile option should be visible and updatable.

Active dates: Enter the dates on which the profile option becomes active/inactive
User Access:

Visible – Determines whether your end users can see and query this profile option in their personal profiles. Otherwise, they cannot query or update values for this option.

Updatable – Determines whether your end users can change the value of this profile option using their Profile Values window. Otherwise, your system administrator must set values for this profile option.

SQL Validation: You can write up a SQL statement to display the selected rows in LOV. If this section is left blank, there will no LOV instead the user can enter any free text as value.

Save the work.

Assign a value to the profile option:
Navigation: System Administrator > Profile > System
Enter the below when the window opens and click on Find button
Under Display section, Choose at what level you want to assign a value.
In the Find field, enter the profile option name to which you want to assign the value.

Enter the value that you want to assign to profile option at the appropriate level and save.

To derive a value of profile option from PL/SQL procedure:
We have seen how to create and assign value to profile option. Most of the times, custom concurrent program will need to get the value of a profile option that will determine its course of actions. You will use API called FND_PROFILE.

1.To get the value of profile option:

select FND_PROFILE.value('ERPSCHOOLS DEBUG') from dual; 

Remember to pass on the name of the profile option and not the user profile name here.

2. To set value to profile option from PL/SQL procedure:

Begin

FND_PROFILE.put ('ERPSCHOOLS DEBUG','No');

end;

Wednesday 18 June 2014

SHELL SCRIPT

set echo off
set pause off
set feed off
set veri off
set termout off
set linesize 6888
set pages 0
set serveroutput on size 999999

spool /u03/extracts/XXCG_Royalty_Review_extract.csv

select 'Order Year'||','||
'Item Type'||','||
'Organization'||','||
'Order #'||','||
'Order Type'||','||
'Customer Name'||','||
'Customer Number'||','||
'Sales Pro Name'||','||
'Sales Pro #'||','||
'Order Status'||','||
'Rec Interface Date'||','||
'Item name'||','||
'Item Description'||','||
'Qty'||','||
'UOM'||','||
'Sell Price'||','||
'Total Sale'||','||
'Ship Date'||','||
'Total Commission'||','||
'Customer Class'||','||
'Invoice Number'||','||
'Invoice Date'||','
from dual;

select soh.attribute1||','||
decode(msi.segment1,
'AT','Custom Signet',
'AS','Royalty Stole',
'Stole Of Gratitude')||','||
decode(soh.ship_from_org_id,104,'ARC','CHA')||','||
to_char(soh.order_number)||','||
sot.name ||','||
replace(hp.party_name,',','')||','||
hca.account_number||','||
replace(rs.name,',','')||','||
rs.salesrep_number||','||
XXCG_order_status(soh.order_number) ||','||
decode(XXCG_order_status(soh.order_number),'Receivables Interfaced',
trunc(XXCG_order_status_date(soh.order_number))) ||','||
substr(msi.segment1,1,2)||'.'||msi.segment2 ||','||
msi.description ||','||
to_char(sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) ||','||
sol.ORDER_QUANTITY_UOM ||','||
to_char(ccd.total_sell_price) ||','||
to_char((sol.ordered_quantity - nvl(sol.cancelled_quantity,0))
* ccd.total_sell_price) ||','||
XXCG_ship_date(soh.header_id) ||','||
to_char(ccd.total_line_commission) ||','||
hca.customer_class_code ||','||
rct.trx_number||','||
rct.trx_date||','
from hz_parties hp,
hz_cust_accounts hca,
oe_transaction_types_tl sot,
oe_order_headers_all soh,
ra_customer_trx_all rct,
jtf_rs_salesreps rs,
XXCG_commissions_details ccd,
oe_order_lines_all sol,
ra_customer_trx_lines_all rctl,
mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
( select ltrim(xxcg_parameter('&&1',',',1)) as order_year
,decode(xxcg_parameter('&&1',',',2),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,1)+ 1,
instr('&&1',',',1,2) - instr('&&1',',',1,1) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_from
,decode(xxcg_parameter('&&1',',',3),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,2)+ 1,
instr('&&1',',',1,3) - instr('&&1',',',1,2) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_to
,xxcg_parameter('&&1',',',4) status
from dual) fltr
where mc.segment1 in (
select XXCG_parameter(fvl.flex_value,'.',1)
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mc.segment1||'.'||mc.segment2||'.'||mc.segment3 in (
select fvl.flex_value
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mic.organization_id = 103
and mic.category_set_id = 1
and mc.CATEGORY_ID = mic.CATEGORY_ID
and mic.INVENTORY_ITEM_ID+0 = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and sol.inventory_item_id = msi.inventory_item_id
and soh.attribute1 = to_char(fltr.order_year)
and (sot.name Like 'Accessories%'
or sot.name Like 'ClassKeeper%'
or sot.name Like 'ClassRental%')
and soh.order_category_code = 'ORDER'
and exists (select null
from dual
where ( apps.XXCG_order_year = fltr.order_year
or (apps.XXCG_order_year != fltr.order_year
and soh.flow_status_code IN ('INVOICED','CLOSED'))))
and sol.header_id = soh.header_id
and soh.salesrep_id = rs.salesrep_id
and soh.order_type_id = sot.transaction_type_id
AND soh.sold_to_org_id = hca.cust_account_id
AND hp.party_id = hca.party_id
and sol.line_id = ccd.line_id
and sol.line_id = rctl.interface_line_attribute6
and rctl.customer_trx_id+0 = rct.customer_trx_id
and trunc(rct.trx_date) between nvl(fltr.date_from,trunc(rct.trx_date)) and nvl(fltr.date_to, trunc(rct.trx_date))
and nvl(fltr.status, 'RECEIVABLES INTERFACED') = 'Receivables Interfaced'
UNION
select soh.attribute1||','||
decode(msi.segment1,
'AT','Custom Signet',
'AS','Royalty Stole',
'Stole Of Gratitude')||','||
decode(soh.ship_from_org_id,104,'ARC','CHA')||','||
to_char(soh.order_number)||','||
sot.name ||','||
replace(hp.party_name,',','')||','||
hca.account_number||','||
replace(rs.name,',','')||','||
rs.salesrep_number||','||
XXCG_order_status(soh.order_number) ||','||
decode(XXCG_order_status(soh.order_number),'Receivables Interfaced',
trunc(XXCG_order_status_date(soh.order_number))) ||','||
substr(msi.segment1,1,2)||'.'||msi.segment2 ||','||
msi.description ||','||
to_char(sol.ordered_quantity - nvl(sol.cancelled_quantity,0)) ||','||
sol.ORDER_QUANTITY_UOM ||','||
to_char(ccd.total_sell_price) ||','||
to_char((sol.ordered_quantity - nvl(sol.cancelled_quantity,0))
* ccd.total_sell_price) ||','||
XXCG_ship_date(soh.header_id) ||','||
to_char(ccd.total_line_commission) ||','||
hca.customer_class_code ||','||
' ,'||
' ,'
from hz_parties hp,
hz_cust_accounts hca,
oe_transaction_types_tl sot,
oe_order_headers_all soh,
jtf_rs_salesreps rs,
XXCG_commissions_details ccd,
oe_order_lines_all sol,
mtl_system_items msi,
mtl_item_categories mic,
mtl_categories mc,
( select ltrim(xxcg_parameter('&&1',',',1)) as order_year
,decode(xxcg_parameter('&&1',',',2),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,1)+ 1,
instr('&&1',',',1,2) - instr('&&1',',',1,1) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_from
,decode(xxcg_parameter('&&1',',',3),' ','',to_char(to_date( substr('&&1' ,instr('&&1',',',1,2)+ 1,
instr('&&1',',',1,3) - instr('&&1',',',1,2) -1) ,'YYYY/MM/DD'),'DD-MON-RR') ) date_to
,xxcg_parameter('&&1',',',4) status
from dual) fltr
where mc.segment1 in (
select XXCG_parameter(fvl.flex_value,'.',1)
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mc.segment1||'.'||mc.segment2||'.'||mc.segment3 in (
select fvl.flex_value
from fnd_flex_values_vl fvl,
fnd_flex_value_sets fvs
where fvs.FLEX_VALUE_SET_NAME = 'XXCG_ROYALTY_CATEGORY_VALUES'
and fvl.FLEX_VALUE_SET_ID = fvs.FLEX_VALUE_SET_ID
and fvl.end_date_active is null )
and mic.organization_id = 103
and mic.category_set_id = 1
and mc.CATEGORY_ID = mic.CATEGORY_ID
and mic.INVENTORY_ITEM_ID+0 = msi.inventory_item_id
and mic.organization_id = msi.organization_id
and sol.inventory_item_id = msi.inventory_item_id
and soh.attribute1 = to_char(fltr.order_year)
and (sot.name Like 'Accessories%'
or sot.name Like 'ClassKeeper%'
or sot.name Like 'ClassRental%')
and soh.order_category_code = 'ORDER'
and sol.header_id = soh.header_id
and soh.salesrep_id = rs.salesrep_id
and soh.order_type_id = sot.transaction_type_id
AND soh.sold_to_org_id = hca.cust_account_id
AND hp.party_id = hca.party_id
and sol.line_id = ccd.line_id
and soh.flow_status_code not in ('INVOICED','CLOSED')
and XXCG_order_status(soh.order_number) = nvl(fltr.status,XXCG_order_status(soh.order_number))
and fltr.date_from is null
and fltr.date_to is null
;

spool off
rollback;

Tuesday 4 March 2014

AP Module

Oracle Payables has following major processes:

Setting up suppliers
Entering and approving invoices
Paying invoices
Creating accounting entries
Sending transactions to the general ledger

Type of Invoices in AP:

Standard: An invoice from a supplier representing an amount due for goods or services purchased (standard invoices can be matched to either a PO or receipt, or not matched)
Credit Memo: A memo from a supplier representing a credit amount toward goods or services for which you have already been invoiced
Debit Memo: An invoice you enter to record a credit for a supplier who does not send you a credit memo
Expense Report: An invoice representing an amount due to an employee for business-related expenses
Prepayment: A type of invoice you enter to pay an advance payment for expenses to a supplier or employee
Withholding Tax Invoices: This is the invoice created on an original invoice to withhold certain amount from the original invoice and pay this amount to a 3rd party (usually Government) later.
Recurring Invoices: for fixed amount and fixed duration, we will select recurring

invoices. Like rent accounts and so on.

8. Mixed Type Invoice: for company miscellaneous expenses we will create mixed

Type.

9. Quick Match Invoice: we will match the invoice either with PO or invoice with

receipt, as the matching payments will be done.

10. PO Default: we will give the PO Number as per the PO amount invoice will be

Generated.

How to create the invoices:

Go to Invoices -> Invoices

Note: By Default the payment option is check.

You can check the Invoice details from backend

select * from ap_invoices_all where invoice_id = &Invoice_Id

select * from ap_invoices_all where invoice_num='&Invoice_Num'

You can find the invoice_id for the invoice you have created from the front end

Go to the invoice screen and go to Help->Diagnostic->Examine

Then In Examine-Field-Prompt type Invoice Id then it will give you the invoice id of the screen shown.

Next step.

Distributions -> Enter the type and amount

This distribution is nothing but the total amount of items listed here and which should be equal to the header’s amount. In distribution we can list all of items with the respective amount

We can see the distribution of our items in the below query:

select * from ap_invoice_distributions_all where invoice_id = &Invoice_id

Now we need to validate out invoices.

Go to the action tab Actions....1 tab in the invoices screen(Header screen) and check the validate check box and then click OK.

To remove the withholding tax:

need to go to the supplier and go to the withholding tab à Then search for your supplier and uncheck the withholding box, then it will not show you again.

Note: If you don’t want to allow the withholding tax then we need to go to the supplier and search for our supplier and then need to go in the invoices and withholding tax tab and then uncheck the tab…..similarly we can do this for the sites tab in the same screen.

If we do some validation/re-validation, then one record will be created into “ap_accounting_events_all” table

select * from ap_accounting_events_all where source_id= &Invoice_id

Note:

Source_id is ‘invoice_id’ in case of Invoices and ‘check_id’ in case of Payments.

If we give some wrong inputs in “Distribution” screen as shown in the below screenshot:

Here our total invoice is for rupees 200 and again we are adding here 100 rupees more i.e this distribution amount is exceeding the total invoice amount. So we need to validate it again and when we revalidate one extra record will be added in ap_accounting_events_all with the column “EVENT_TYPE_CODE” as “INVOICE ADJUSTMENT” as seen in the below query:

select * from ap_accounting_events_all where source_id= &Invoice_Id

To reverse this transaction we need to click on the reverse 1 tab then it will add one more record, which will be the reverse of the previous distribution line.

Note: After any addition or deletion in the item list or in accounting we need to re-validate our transaction.

After this we need to validate.

Now we need to pay for our Invoices: PAYMENTS

Go to Payables –> Payments -> Payments

When we want to create a single payment, rather than an entire payment batch, we either record a manual payment we generated outside of Payables, or we can create a Quick payment (single computer generated payment).

Then click to the click “Enter/Adjust Invoice”

Then go to the Invoice Number and query for your invoice:

Now take the check id from the query :

select * from ap_invoice_payments_all where invoice_id = &Invoice_id

and put this check id in the below query :

select * from ap_checks_all where check_id = &check_id

ACCOUNTING:

We need to do the accounting:
Go to the Invoices à Invoices

Search for your invoice Num –> Dhee_Ballu

Go To Actions…1 then check the Create Accounting Box:

2^nd Method:

Go to the View -> Request
Then search for the Reports as “Payables Accounting Process”

Accounting1

Now give the account “From date” and account “To date”:

Then Click on OK and submit the request.

Select * from ap_accounting_events_all where source_id= &invoice_id

to get the “ACCOUNTING_EVENT_ID” here it is (78350 78351)
Go and check these entries in the ap_ae_headers_all

Select * from ap_ae_headers_all where ACCOUNTING_EVENT_ID IN (78350,78351)

Now take AE_HEADER_ID from the ap_ae_headers_all (above query) and check in the ap_ae_lines_all

Select * from ap_ae_lines_all where AE_HEADER_ID IN (74631,74632)

Now we need to put all our transaction to the General Ledger, which is the common repository of all the modules that finally gives the info about the profit and loss / balance sheet.

We need to run the concurrent program for transferring the accounting into the GL and the name of the request is “Payables Transfer to General Ledger” so query for this and

We need to enter the following as mandatory parameter

From date: e.g sysdate

To date: e.g Sysdate

Submit Journal Import: Yes

Transfer to GL Interface: In Detail

Important tables in AP modules

Invoice Table-

· ap_invoices_batches_all

· ap_invoices_all

· ap_invoce_lines_all

· ap_invoice_distributions_all

Supplier Table 11i	Supplier Table R12
po_vendors po_vendor_sites_all po_vendor_contacts	ap_suppliers ap_supplier_sites_all ap_supplier_contact

Payment Table

· ap_invoice_payments_all

· ap_inv_selection_criteria_all

· ap_checks_all

Accounting Table

· ap_accounting_events

· ap_ae_headers_all

· ap_ae_lines_all

Payment terms table

· ap_terms

· ap_terms_lines

Hold_Tables

· ap_holds_all

· ap_hold_lines

· ap_realease

Tolerance table

· ap_tolrance.

Reporting Table

· ap_reporting_ntities

· ap_reporting_entities_lines

Distribution Set Table

· ap_distribution_sets

· ap_distribution_set_lines_all

Bank table

· ap_bannks_branches_all

· ap_bank_accounts_all

· ap_bank_account_uses_all