AMRESH KUMARAN ORACLE APPLICATIONS

Wednesday, 1 July 2015

Purchase Requisition Data Loading

CREATE TABLE APPS.XXSKM_PR_UPLOAD
(
ITEM_CODE VARCHAR2(50 BYTE),
NET_REQ NUMBER(10),
LIST_PRICE_PER_UNIT NUMBER(10),
PRIMARY_UNIT_OF_MEASURE VARCHAR2(50 BYTE),
PREPARER VARCHAR2(50 BYTE),
REQUESTOR VARCHAR2(50 BYTE),
DESTINATION_ORG VARCHAR2(50 BYTE),
DELIVER_TO_LOCATION VARCHAR2(50 BYTE),
NEED_BY_DATE DATE,
CHARGE_ACCOUNTS_ID VARCHAR2(50 BYTE),
PROCESS_FLAG VARCHAR2(2 BYTE),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER,
CREATED_BY NUMBER,
CREATION_DATE DATE DEFAULT SYSDATE,
REQUEST_ID NUMBER
) -- this custom table

CREATE OR REPLACE PACKAGE APPS.xxskm_pr_upload_pkg
AS
--
--------------------------------------------------------------------------------

-- Program Type : Package Body
-- Conc Prog Short Name : xxskm_pr_upload_pkg
--
-- Description:
--
-- Modification History:
-- --------- ----------- ----------------------------------------------------
-- Date Author Comments
-- --------- ----------- ----------------------------------------------------
-- 21-Jul-2014 Amresh Initial Creation
--
--------------------------------------------------------------------------------
--
--
PROCEDURE main ( p_errbuf_out OUT VARCHAR2
, p_retcod_out OUT VARCHAR2
);
--
END xxskm_pr_upload_pkg;

execution file name in xxskm_pr_upload_pkg.main

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

CREATE OR REPLACE PACKAGE BODY APPS.xxskm_pr_upload_pkg
AS
--
--------------------------------------------------------------------------------
-- Owner : HCL Infosystems Ltd
-- Project : Su-Kam
-- Program Type : Package Body
-- Conc Prog Short Name : xxskm_pr_upload_pkg
--
-- Description:
--
-- Modification History:
-- --------- ----------- ----------------------------------------------------
-- Date Author Comments
-- --------- ----------- ----------------------------------------------------
-- 21-Jul-2014 Subir Initial Creation
--
--------------------------------------------------------------------------------
--
pv_conc_req_id NUMBER;
pv_request_id NUMBER := fnd_global.conc_request_id;
pv_user_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('USER_ID'), '1');
--pv_resp_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('RESP_ID'), '1');
--pv_resp_appl_id VARCHAR2(30) := NVL (fnd_profile.VALUE ('RESP_APPL_ID'), '1');
pv_org_id NUMBER := 101;
--
PROCEDURE xxfhl_print_audit_details (p_req_id NUMBER);
--
PROCEDURE main ( p_errbuf_out OUT VARCHAR2
, p_retcod_out OUT VARCHAR2
)
IS
--
v_item_id number;
v_uom varchar2(10);
v_net_req number;
v_list_price number;
v_date date;
v_phase VARCHAR2(200) :='';
v_status VARCHAR2(200) :='';
v_dev_phase VARCHAR2(200) :='';
v_dev_status VARCHAR2(200) :='';
v_message VARCHAR2(200) :='';
v_return BOOLEAN;
--
cursor cur_data is
select a.rowid
, a.item_code
, a.net_req
, a.list_price_per_unit
, a.primary_unit_of_measure
, a.preparer
, a.requestor
, a.destination_org
, a.deliver_to_location
, a.need_by_date
, a.charge_accounts_id
, a.process_flag
, a.last_update_date
, a.last_updated_by
, a.created_by
, a.creation_date
from xxskm_pr_upload a
where a.item_code not like 'W%'
and nvl(a.process_flag,'N') = 'N'
and request_id = pv_request_id
;
--
begin
--
fnd_file.put_line (fnd_file.log,'118');
fnd_file.put_line (fnd_file.log,'delete old records from po_requisitions_interface_all');
--
delete from po_requisitions_interface_all
where created_by = pv_user_id;
--
fnd_file.put_line (fnd_file.log,'Update need_by_date of custom table');
--
update xxskm_pr_upload a
set a.need_by_date = trunc(sysdate) + 3
, a.created_by = pv_user_id
, a.creation_date= SYSDATE
, request_id = pv_request_id
where nvl(a.process_flag,'N') = 'N' ;
--
for c1 in cur_data loop
--
fnd_file.put_line (fnd_file.log,'119');
v_net_req := c1.net_req;
--
fnd_file.put_line (fnd_file.log,'v_net_req'||'--'||v_net_req);
fnd_file.put_line (fnd_file.log,'v_list_price'||'--'||v_list_price);
--
v_date := c1.need_by_date;
--
begin
--
SELECT msi.inventory_item_id
, msi.primary_unit_of_measure
, nvl(msi.list_price_per_unit,1)
INTO v_item_id
, v_uom
, v_list_price
FROM mtl_system_items_kfv msi
WHERE msi.concatenated_segments = c1.item_code
AND organization_id = 106 -- 001
;
--
exception
when others then
--
fnd_file.put_line (fnd_file.log,'Select Statement'||' '||c1.item_code);
--
end;
--
fnd_file.put_line (fnd_file.log,'122');
--
INSERT INTO po_requisitions_interface_all
(interface_source_code
,batch_id
,transaction_id
,org_id
,destination_type_code
,authorization_status
,preparer_id
,source_type_code
,unit_of_measure
,line_type_id
,item_id
,unit_price
,quantity
,destination_organization_id
,deliver_to_location_id
,deliver_to_requestor_id
,multi_distributions
,req_dist_sequence_id
,interface_source_line_id
,charge_account_id
,need_by_date
,destination_subinventory
,creation_date
,created_by
)
values ( 'EXTERNAL' -- interface_source_code
, NULL -- batch_id
, NULL -- transaction_id
, 101 -- org_id
, 'INVENTORY' -- destination_type_code
, 'INCOMPLETE' -- authorization_status
, 7329 -- preparer_id
, 'VENDOR' -- source_type_code
, v_uom -- unit_of_measure
, 1020 -- line_type_id
, v_item_id -- item_id
, v_list_price -- unit_price
, v_net_req -- quantity
, 108 -- destination_organization_id
, 166 -- deliver_to_location_id
, 7329 -- deliver_to_requestor_id
, null -- multi_distributions
, null -- req_dist_sequence_id
, null -- interface_source_line_id
, 1551 -- charge_account_id
, v_date -- need_by_date
, 'RMS' -- destination_subinventory
, sysdate -- creation_date
, pv_user_id -- created_by
);
commit;
--
update xxskm_pr_upload a
set process_flag = 'P'
, last_update_date = SYSDATE
, last_updated_by = pv_user_id
where rowid = c1.rowid
;
--
EXIT WHEN cur_data%NOTFOUND;
fnd_file.put_line (fnd_file.log,'123');
--
end loop;
--
--------------------------
-- Run Requisition Import
--------------------------
fnd_file.put_line (fnd_file.log,' Submiting the request for Requisition Import ');
--
fnd_request.set_org_id(pv_org_id);
--
pv_conc_req_id := fnd_request.submit_request(Application => 'PO'
,Program => 'REQIMPORT'
,Description => NULL
,Start_Time => NULL
,Sub_Request => FALSE
,Argument1 => NULL
,Argument2 => NULL
,Argument3 => 'VENDOR'
,Argument4 => NULL
,Argument5 => 'N'
,Argument6 => 'N' );
COMMIT;
--
fnd_file.put_line (fnd_file.log,' Waiting for completion of Requisition Import ');
--
-- Calling function fnd_concurrent.wait_for_request so that interface gets completed
-- when the submitted request is completed.
v_return := fnd_concurrent.wait_for_request(pv_conc_req_id
,10
,0
,v_phase
,v_status
,v_dev_phase
,v_dev_status
,v_message
);
--
---------------------------
-- Output detail procedure
---------------------------
fnd_file.put_line (fnd_file.log,'Print Audit Details Start Date Time :- '|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.log, '');
--
xxfhl_print_audit_details (p_req_id => pv_conc_req_id);
--
fnd_file.put_line (fnd_file.log,'Print Audit Details End Date Time :- '|| TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS'));
fnd_file.put_line (fnd_file.log, '');
--

exception
when others then
--
fnd_file.put_line (fnd_file.log,'Records are not uploaded');
--
end main;
--
--
--------------------------------------------------------------------------------
-- Name : xxfhl_print_audit_details
-- Description:
-- This procedure prints audit details report
--
--
-- Parameters:
-- 1. p_req_id
--
-- Pseudo logic:
-- None
--------------------------------------------------------------------------------
--
PROCEDURE xxfhl_print_audit_details (p_req_id NUMBER)
IS
--
--
CURSOR cur_error_details
IS
SELECT NVL(TRIM(prh.segment1), ' ') pr_num
, prh.creation_date
, NVL(TRIM(msi.concatenated_segments), ' ') item_code
, NVL(TRIM(prl.unit_price), ' ') unit_price
, NVL(TRIM(prl.quantity), ' ') quantity
, NVL(TRIM(fu.user_name), ' ') user_name
, NULL error_message
, 'P' process_flag
FROM po_requisition_headers_all prh
, po_requisition_lines_all prl
, mtl_system_items_kfv msi
, fnd_user fu
WHERE prh.requisition_header_id = prl.requisition_header_id
AND prl.item_id = msi.inventory_item_id
AND prl.destination_organization_id = msi.organization_id
AND trunc(prh.creation_date) = trunc(sysdate)
AND fu.user_id = prh.created_by
AND prh.request_id = p_req_id
--
UNION
--
SELECT null pr_num
, pri.creation_date
, NVL(TRIM(msi.concatenated_segments), ' ') item_code
, NVL(TRIM(pri.unit_price), ' ') unit_price
, NVL(TRIM(pri.quantity), ' ') quantity
, null user_name
, NVL(TRIM(pie.error_message), ' ') error_message
, 'E' process_flag
FROM po_requisitions_interface_all pri
, po_interface_errors pie
, mtl_system_items_kfv msi
, fnd_user fu
WHERE trunc(pri.need_by_date) = trunc(sysdate)
AND pie.interface_transaction_id = pri.transaction_id
AND pri.item_id = msi.inventory_item_id
AND pri.destination_organization_id = msi.organization_id
AND pri.request_id = p_req_id
ORDER BY 1
;
--
BEGIN
--
fnd_file.put_line(fnd_file.log,'xpad-001');
fnd_file.put_line(fnd_file.log,'xxfhl_print_audit_details' );
--
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, '');
--
fnd_file.put_line(fnd_file.log, 'xpad-050');
fnd_file.put_line (fnd_file.output, '--------------Detail------------');
fnd_file.put_line (fnd_file.output, '');
fnd_file.put_line (fnd_file.output, 'Pr Number Item Code Unit Price Quantity Created By Creation Dt Error Desc');
fnd_file.put_line (fnd_file.output, '------------- ------------------------------ ---------------------- ---------------------- ---------------------- ----------- ----------------------');
--
FOR crec IN cur_error_details
LOOP
--
fnd_file.put_line (fnd_file.output
, RPAD (crec.pr_num, 13) ||' '||
RPAD (crec.item_code, 31) ||' '||
RPAD (crec.unit_price, 22) ||' '||
RPAD (crec.quantity, 22) ||' '||
RPAD (crec.user_name, 22) ||' '||
RPAD (crec.creation_date, 22) ||' '||
crec.error_message
);
--
IF crec.process_flag = 'E'
THEN
--
update xxskm_pr_upload a
set process_flag = 'E'
where item_code = crec.item_code
and net_req = crec.quantity
;
--
END IF;
--
END LOOP;
--
COMMIT;
--
fnd_file.put_line(fnd_file.log, 'xpad-110');
fnd_file.put_line(fnd_file.output, ' ' );
fnd_file.put_line(fnd_file.log, 'xpad-999');
--
EXCEPTION
--
WHEN OTHERS THEN
--
fnd_file.put_line(fnd_file.log, 'Error in := xxfhl_print_audit_details, '||sqlerrm );
--
END xxfhl_print_audit_details;
--
END xxskm_pr_upload_pkg;
/

Invoice TO Payments Based On Gl Code

/* Formatted on 2015/06/29 16:14 (Formatter Plus v4.8.8) */

(SELECT (CASE

WHEN acl.currency_code = 'USD'

THEN aia.exchange_rate * aipl.amount

WHEN acl.currency_code = 'EUR'

THEN aia.exchange_rate * aipl.amount

WHEN acl.currency_code = 'HKD'

THEN aia.exchange_rate * aipl.amount

WHEN acl.currency_code = 'AED'

THEN aia.exchange_rate * aipl.amount

ELSE aipl.amount

END

) payment_amount,aia.gl_date gl_date_invoice ,

aia.invoice_num, aia.invoice_amount , aia.invoice_id ,aia.invoice_date , aia.voucher_num, acl.check_number, acl.check_id,

asp.vendor_name supplier_name, asp.segment1 supplier_number,

aia.accts_pay_code_combination_id account_id, gk.segment3 segment3,aipl.accounting_date gl_date_payment,

acl.status_lookup_code

FROM ap_checks_all acl,

ap_invoice_payments_all aipl,

ap_invoices_all aia,

ap_suppliers asp,

gl_code_combinations gk

WHERE aipl.check_id = acl.check_id

AND aipl.invoice_id = aia.invoice_id

AND aia.vendor_id = asp.vendor_id(+)

AND aia.accts_pay_code_combination_id = gk.code_combination_id

AND aipl.accounting_date BETWEEN :p_from_date AND :p_to_date

AND gk.segment7 = 321002

AND gk.segment1 = 10

AND aipl.check_id IN ('243643'))

Thursday, 7 May 2015

Account Analysis Report Internal

SELECT Distinct JH.DESCRIPTION,
jl.description Line_Desc,
SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
SUBSTR (jh.je_source, 1, 15) SOURCE,
TO_CHAR (jl.effective_date, 'DD-MON-YY') Gl_Date,
jh.NAME Journal_Name,
-- jh.je_batch_id batch,
SUBSTR (ds.NAME, 1, 13) lsequence,
jh.doc_sequence_value hnumber,
decode(xal.ACCOUNTED_DR,null,jl.accounted_dr,xal.ACCOUNTED_DR ) Acc_Dr ,
decode(xal.ACCOUNTED_CR,null,jl.accounted_Cr,xal.ACCOUNTED_CR ) Acc_Cr,
decode(xal.ENTERED_DR,null,jl.ENTERED_dr,xal.ENTERED_DR ) Ent_Dr,
decode(xal.ENTERED_CR,null,jl.ENTERED_Cr,xal.ENTERED_CR ) Ent_Cr,
jh.external_reference REFERENCE,
jl.period_name Period_name,
ir.subledger_doc_sequence_id seq_id,
ds.NAME Voucher_Category,
DECODE (ir.subledger_doc_sequence_value ,NULL,JH.DOC_SEQUENCE_VALUE,ir.subledger_doc_sequence_value ) Subled_Seq_No,
-- jh.doc_sequence_id h_seq_id,
DECODE (xah.DOC_SEQUENCE_VALUE,NULL,JH.DOC_SEQUENCE_VALUE,xah.DOC_SEQUENCE_VALUE) DOC_SEQUENCE_VALUE,
xal.PARTY_ID,
xal.code_combination_id,
gk.concatenated_segments Acct_Code,
gk.segment6 Natural_Acct,
gk.segment1,
gk.segment3,
gk.segment5,
gk.segment7,
decode(fu.DESCRIPTION,null ,fu.USER_NAME,fu.DESCRIPTION) user_name ,
apsu.VENDOR_NAME,
apsu.SEGMENT1 Vendor_code,
xal.CURRENCY_CODE Currency,
xal.CURRENCY_CONVERSION_RATE Rate,
arc.CUSTOMER_NAME,
arc.CUSTOMER_NUMBER,
xlate.SOURCE_ID_INT_1 Invoice_ID,
xlate.ENTITY_CODE,
xlate.TRANSACTION_NUMBER--, b.daily_balance
, ir.gl_sl_link_id
FROM gl_je_lines jl,
gl_je_headers jh,
gl_je_batches jb,
gl_je_sources js,
gl_je_categories jc,
gl_import_references ir,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL,
ap_suppliers apsu,
ar_customers arc,
fnd_document_sequences ds,
xla_events xlae,
xla_transaction_entities xlate,
fnd_user fu,
gl_code_combinations_kfv gk
WHERE jl.status = 'P'
-- AND jl.code_combination_id = :P_ACCT
and xal.code_combination_id= gk.code_combination_id
and jh.created_by=fu.user_id
AND jl.ledger_id = :p_set_of_bks_id
AND jh.status = 'P'
AND jh.actual_flag = 'A'
AND jh.je_header_id = jl.je_header_id
-- &C_WHERE_CURR
AND jb.je_batch_id = jh.je_batch_id
AND jb.average_journal_flag = 'N'
AND js.je_source_name = jh.je_source
AND jc.je_category_name = jh.je_category
AND ir.je_header_id(+) = jl.je_header_id
AND ir.je_line_num(+) = jl.je_line_num
AND ds.doc_sequence_id(+) = ir.subledger_doc_sequence_id
and XAH.AE_HEADER_ID(+)=XAL.AE_HEADER_ID
AND XAL.GL_SL_LINK_ID(+)=IR.GL_SL_LINK_ID
and arc.CUSTOMER_ID(+)=xal.PARTY_ID
AND XAL.GL_SL_LINK_TABLE(+)=IR.GL_SL_LINK_TABLE
and xah.EVENT_ID=xlae.EVENT_ID(+)
and xlae.ENTITY_ID=xlate.ENTITY_ID(+)
and apsu.VENDOR_ID(+)=xal.PARTY_ID
-- AND jl.period_name in(:P_START_PERIOD,:P_END_PERIOD)
and jh.DEFAULT_EFFECTIVE_DATE between :p_from_date and :p_to_date
and gk.segment1 = nvl(:P_Comp,gk.segment1)
and gk.segment3 = nvl(:P_Branch,gk.segment3)
and gk.segment5 = nvl(:P_Prod,gk.segment5)
and gk.segment7=nvl(:P_Acct,gk.segment7)
and gk.segment6= nvl(:P_Dept,gk.segment6)
--and jh.je_header_id=116193
-- &WHERE_FLEX
UNION ALL
select JH.DESCRIPTION,
jl.description Line_Desc,
SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
SUBSTR (jh.je_source, 1, 15) SOURCE,
TO_CHAR (jl.effective_date, 'DD-MON-YY') Gl_Date,
jh.NAME Journal_Name,
-- jh.je_batch_id batch,
Null lsequence,
jh.doc_sequence_value hnumber,
jl.accounted_dr Acc_Dr ,
jl.accounted_Cr Acc_Cr,
jl.ENTERED_dr Ent_Dr,
jl.ENTERED_Cr Ent_Cr,
jh.external_reference REFERENCE,
jl.period_name Period_name,
jr.subledger_doc_sequence_id seq_id,
null Voucher_Category,
DECODE (jr.subledger_doc_sequence_value ,NULL,JH.DOC_SEQUENCE_VALUE,jr.subledger_doc_sequence_value ) Subled_Seq_No,
-- jh.doc_sequence_id h_seq_id,
JH.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
Null PARTY_ID,
jl.code_combination_id,
gk.concatenated_segments Acct_Code,
gk.segment6 Natural_Acct,
gk.segment1,
gk.segment3,
gk.segment5,
gk.segment7,
fu.description,
Null VENDOR_NAME,
null vendor_code,
jh.CURRENCY_CODE Currency,
jh.CURRENCY_CONVERSION_RATE Rate,
Null CUSTOMER_NAME,
null customer_number,
Null Invoice_ID,
Null ENTITY_CODE,
Null TRANSACTION_NUMBER,
jr.gl_sl_link_id
from gl_je_headers jh,
gl_je_lines jl,
gl_je_batches jb,
gl_je_sources js ,
gl_je_categories jc,
gl_import_references jr,
gl_code_combinations_kfv gk,
fnd_user fu
WHERE js.JE_SOURCE_NAME IN('Manual','Spreadsheet','Purchasing India')
and jl.JE_HEADER_ID=jh.JE_HEADER_ID
and jh.JE_BATCH_ID=jb.JE_BATCH_ID(+)
and jh.JE_SOURCE(+)=js.JE_SOURCE_NAME
and jh.JE_CATEGORY=jc.JE_CATEGORY_NAME(+)
and jr.JE_HEADER_ID(+)=jl.JE_HEADER_ID
and jr.JE_LINE_NUM(+)=jl.JE_LINE_NUM
and jl.CODE_COMBINATION_ID=gk.CODE_COMBINATION_ID
and jh.CREATED_BY=fu.USER_ID
--and jh.PERIOD_NAME in(:P_START_PERIOD,:P_END_PERIOD)
and jh.DEFAULT_EFFECTIVE_DATE between :p_from_date and :p_to_date
and jh.STATUS='P'
and jl.STATUS='P'
--and jh.ACTUAL_FLAG='Y'
AND jl.ledger_id = :p_set_of_bks_id
AND jb.average_journal_flag = 'N'
and gk.segment1 = nvl(:P_Comp,gk.segment1)
and gk.segment3 = nvl(:P_Branch,gk.segment3)
and gk.segment5 = nvl(:P_Prod,gk.segment5)
and gk.segment7=nvl(:P_Acct,gk.segment7)
and gk.segment6= nvl(:P_Dept,gk.segment6)
--and jh.je_header_id=116193
-- &WHERE_FLEX
ORDER BY 5

Saturday, 21 March 2015

Document Sequences Inventory Query

/* Formatted on 2015/03/21 15:01 (Formatter Plus v4.8.8) */
SELECT jrd.registration_num, jrd.effective_from, jrd.effective_to,
hao.NAME organization_name, hl.description location_name, jl.meaning organization_type,
(CASE
WHEN jrds.document_class = 'UD'
THEN 'Unregistered - Default'
WHEN jrds.document_class = 'D'
THEN 'Default'
WHEN jrds.document_class = 'O'
THEN 'Order'
END
) document_class, (case when ott.name is null THEN 'Default' else ott.name end) Document_type_name ,
jrds.start_number, jrds.current_number
FROM jai_rgm_doc_seq_hdrs jrd,
jai_rgm_doc_seq_dtls jrds,
hr_all_organization_units hao,
hr_locations hl ,
oe_transaction_types_tl ott ,
ja_lookups jl
WHERE jrd.registration_num = nvl(:P_registration_num ,jrd.registration_num ) --'06291922190'
AND TRUNC (jrd.effective_from) BETWEEN :p_from_date AND :p_to_date
AND jrds.rgm_document_seq_id = jrd.rgm_document_seq_id
AND hao.organization_id = jrd.party_id
AND hl.location_id = jrd.party_site_id
and ott.transaction_type_id(+) = jrds.DOCUMENT_CLASS_TYPE_ID
and jrd.ORGANIZATION_TYPE = jl.lookup_code

Monday, 26 January 2015

TO Find the Application ID for Which Responsibility

sELECT fa.application_id "Application ID",

fat.application_name "Application Name",

fa.application_short_name "Application Short Name",

fa.basepath "Basepath"

FROM fnd_application fa,

fnd_application_tl fat

WHERE fa.application_id = fat.application_id

AND fat.language = USERENV('LANG')

Wednesday, 21 January 2015

GOOD IN TRANSIT REPORT AND QUERY WITH GENERAL LEDGER

SELECT gl_date, SOURCE, CATEGORY, doc_sequence_value gl_document, user_name,
location_name, location_code,
(CASE
WHEN order_number_rec IS NULL
THEN order_number
ELSE order_number_rec
END
) order_number, (case when PO_REQ_RECE is null then PR_REQ else PO_REQ_RECE end) pr_requisition_number,
receipt_num, gl_code_acct,
(CASE
WHEN accounting_class_code = 'INTRANSIT_VALUATION'
THEN 'GOODS IN TRANSIT'
END
) descp,
gl_code_id, joual_name, line_desc, accounted_dr, accounted_cr, period_nme
FROM(SELECT SUBSTR (jc.user_je_category_name, 1, 26) CATEGORY,
t1.period_name period_nme,
DECODE (t7.doc_sequence_value,
NULL, t1.doc_sequence_value,
t7.doc_sequence_value
) doc_sequence_value,
( t3.segment1
|| '.'
|| t3.segment2
|| '.'
|| t3.segment3
|| '.'
|| t3.segment4
|| '.'
|| t3.segment5
|| '.'
|| t3.segment6
|| '.'
|| t3.segment7
|| '.'
|| t3.segment8
|| '.'
|| t3.segment9
|| '.'
|| t3.segment10
) gl_code_acct,
(SELECT DISTINCT haotl.NAME
FROM hr_all_organization_units hao,
hr_all_organization_units_tl haotl
WHERE DECODE
(hr_security.view_all,
'Y', 'TRUE',
hr_security.show_record
('HR_ALL_ORGANIZATION_UNITS',
haotl.organization_id
)
) = 'TRUE'
AND DECODE (hr_general.get_xbg_profile,
'Y', hao.business_group_id,
hr_general.get_business_group_id
) = hao.business_group_id
AND hao.organization_id = haotl.organization_id
AND haotl.LANGUAGE = USERENV ('LANG')
AND SUBSTR (hao.attribute1, 1, 4) =
TO_CHAR (t3.segment3))
location_name,
(SELECT DISTINCT hu.attribute1
FROM hr_all_organization_units hu
WHERE SUBSTR (hu.attribute1, 1, 4) =
TO_CHAR (t3.segment3))
location_code,
NVL (t2.accounted_dr, 0) accounted_dr,
NVL (t2.accounted_cr, 0) accounted_cr, xteu.transaction_number,
t1.description, t2.description line_desc,
SUBSTR (t1.je_source, 1, 15) SOURCE,
TO_CHAR (t2.effective_date, 'DD-MON-YY') gl_date,
t1.NAME joual_name, t1.doc_sequence_value hnumber,
SUBSTR (t1.NAME, 1, 8) journal_name, mmt.transaction_id,
t1.external_reference REFERENCE, t2.period_name period_name,
mmt.organization_id, mmt.inventory_item_id,
mmt.rcv_transaction_id, t2.effective_date,
(SELECT b.receipt_num
FROM rcv_transactions a,
rcv_shipment_headers b
WHERE a.shipment_header_id =
b.shipment_header_id
AND a.transaction_id = mmt.rcv_transaction_id
AND ROWNUM < 2) receipt_num,
(SELECT e.segment1
FROM rcv_transactions a,
rcv_shipment_headers b,
rcv_shipment_lines c,
po_requisition_lines_all d,
po_requisition_headers_all e
WHERE a.shipment_header_id = b.shipment_header_id
AND c.shipment_header_id = b.shipment_header_id
AND c.requisition_line_id = d.requisition_line_id
AND e.requisition_header_id = d.requisition_header_id
AND a.transaction_id = mmt.rcv_transaction_id
AND ROWNUM < 2) po_req_rece,
(SELECT TO_CHAR (f.order_number)
FROM rcv_transactions a,
rcv_shipment_headers b,
rcv_shipment_lines c,
po_requisition_lines_all d,
po_requisition_headers_all e,
oe_order_headers_all f
WHERE a.shipment_header_id = b.shipment_header_id
AND c.shipment_header_id = b.shipment_header_id
AND c.requisition_line_id = d.requisition_line_id
AND e.requisition_header_id = d.requisition_header_id
AND e.segment1 = f.orig_sys_document_ref
AND a.transaction_id = mmt.rcv_transaction_id
AND ROWNUM < 2) order_number_rec,
t5.accounting_class_code, wdd.transaction_id delive,
wdd.source_line_id, mmt.trx_source_line_id,
wdd.source_header_number order_number,
(SELECT orig_sys_document_ref
FROM oe_order_headers_all ooha
WHERE order_number = wdd.source_header_number) pr_req,
t5.currency_code currency, t5.currency_conversion_rate rate,
xteu.entity_code,
DECODE (fu.description,
NULL, fu.user_name,
fu.description
) user_name,
t3.code_combination_id gl_code_id
FROM gl_je_headers t1,
gl_je_lines t2,
gl_code_combinations t3,
gl_import_references t4,
xla_ae_lines t5,
xla_ae_headers t7,
xla_distribution_links t6,
xla_transaction_entities_upg xteu,
mtl_material_transactions mmt,
wsh_delivery_details wdd,
gl_je_categories jc,
fnd_user fu
WHERE t1.je_header_id = t2.je_header_id
AND jc.je_category_name = t1.je_category
AND t1.created_by = fu.user_id
AND t2.je_header_id = t4.je_header_id
AND t2.je_line_num = t4.je_line_num
AND t4.gl_sl_link_id = t5.gl_sl_link_id
AND t4.gl_sl_link_table = t5.gl_sl_link_table
AND t5.ae_header_id = t6.ae_header_id
AND t5.ae_header_id = t7.ae_header_id
AND t5.ae_line_num = t6.ae_line_num
AND t2.ledger_id = 2021
AND t2.code_combination_id = t3.code_combination_id
AND t2.effective_date BETWEEN :p_date_from AND :p_date_to
AND t3.segment3 = NVL (:p_segment3, t3.segment3)
AND t3.segment7 = NVL (:p_account_from, t3.segment7)
AND mmt.trx_source_line_id = wdd.source_line_id(+)
AND t7.entity_id = xteu.entity_id
AND t5.accounting_class_code IN ('INTRANSIT_VALUATION')
AND TO_CHAR (mmt.transaction_id) = xteu.transaction_number)

Tuesday, 13 January 2015

Inventory Aging Report as ON Stock

/* Formatted on 2014/12/16 10:51 (Formatter Plus v4.8.8) */
SELECT NAME, organization_name, item_code, item_type, uom,
subinventory_code, description, inventory_item_id, organization_id,
item_cost, on_hand_quantity, CASE
WHEN b1 < 0
THEN 0
ELSE b1
END days, CASE
WHEN b2 < 0
THEN 0
ELSE b2
END days1, CASE
WHEN b3 < 0
THEN 0
ELSE b3
END days2, CASE
WHEN b4 < 0
THEN 0
ELSE b4
END days3, CASE
WHEN b5 < 0
THEN 0
ELSE b5
END days4, CASE
WHEN b6 < 0
THEN 0
ELSE b6
END days5, CASE
WHEN b7 < 0
THEN 0
ELSE b7
END days6, total
FROM (SELECT NAME, organization_name, item_code, item_type, uom,
subinventory_code, description, inventory_item_id,
organization_id, item_cost, on_hand_quantity, issued_qty,
(CASE
WHEN ( bucket2
+ bucket3
+ bucket4
+ bucket5
+ bucket6
+ bucket7
+ issued_qty
) < 0
THEN ( bucket1
+ bucket2
+ bucket3
+ bucket4
+ bucket5
+ bucket6
+ bucket7
+ issued_qty
)
ELSE bucket1
END
) b1,
(CASE
WHEN ( bucket3
+ bucket4
+ bucket5
+ bucket6
+ bucket7
+ issued_qty
) < 0
THEN ( bucket2
+ bucket3
+ bucket4
+ bucket5
+ bucket6
+ bucket7
+ issued_qty
)
ELSE bucket2
END
) b2,
(CASE
WHEN (bucket4 + bucket5 + bucket6 + bucket7 + issued_qty
) < 0
THEN ( bucket3
+ bucket4
+ bucket5
+ bucket6
+ bucket7
+ issued_qty
)
ELSE bucket3
END
) b3,
(CASE
WHEN (bucket5 + bucket6 + bucket7 + issued_qty) < 0
THEN (bucket4 + bucket5 + bucket6 + bucket7
+ issued_qty
)
ELSE bucket4
END
) b4,
(CASE
WHEN (bucket6 + bucket7 + issued_qty) < 0
THEN (bucket5 + bucket6 + bucket7 + issued_qty)
ELSE bucket5
END
) b5,
(CASE
WHEN (bucket7 + issued_qty) < 0
THEN (bucket6 + bucket7 + issued_qty)
ELSE bucket6
END
) b6,
(bucket7 + issued_qty) b7, total
FROM (SELECT NAME, organization_name, item_code, item_type, uom,
subinventory_code, description, inventory_item_id,
organization_id, item_cost, on_hand_quantity,
bucket1, bucket2, bucket3, bucket4, bucket5, bucket6,
bucket7,
(SELECT NVL (SUM (mmt.primary_quantity),
0
)
FROM mtl_material_transactions mmt
WHERE 1 = 1
AND mmt.organization_id = z.organization_id
AND mmt.inventory_item_id = z.inventory_item_id
AND mmt.subinventory_code = z.subinventory_code
AND mmt.primary_quantity < 0
AND TRUNC (mmt.transaction_date) <=
TRUNC (:p_date))
issued_qty,
total
FROM (SELECT a.NAME, a.organization_name, a.item_code,
a.item_type, a.uom, a.subinventory_code,
a.description, a.inventory_item_id,
a.organization_id, a.item_cost,
a.on_hand_quantity,
--(a.item_cost * a.on_hand_quantity) total
total,
((SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id =
a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN
(10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date)
BETWEEN TRUNC (:p_date) - 30
AND TRUNC (:p_date)))
bucket1,
((SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id =
a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN
(10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date)
BETWEEN TRUNC (:p_date) - 60
AND TRUNC (:p_date) - 31))
bucket2,
(SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN (10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date)
BETWEEN TRUNC (:p_date) - 90
AND TRUNC (:p_date) - 61)
bucket3,
(SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN (10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date)
BETWEEN TRUNC (:p_date) - 180
AND TRUNC (:p_date) - 91)
bucket4,
(SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN (10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date)
BETWEEN TRUNC (:p_date) - 240
AND TRUNC (:p_date) - 181)
bucket5,
(SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN (10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date)
BETWEEN TRUNC (:p_date) - 360
AND TRUNC (:p_date) - 241)
bucket6,
(SELECT NVL (SUM (primary_quantity),
0
)
FROM mtl_material_transactions b
WHERE 1 = 1
AND b.inventory_item_id =
a.inventory_item_id
AND b.organization_id = a.organization_id
AND b.subinventory_code =
a.subinventory_code
AND b.transaction_action_id NOT IN
(24, 30)
AND b.transaction_type_id NOT IN (10008)
AND b.primary_quantity > 0
AND TRUNC (transaction_date) <
TRUNC (:p_date)
- 360) bucket7
FROM (SELECT msi.concatenated_segments
item_code,
msi.item_type, hou.NAME,
ood.organization_name,
msi.primary_unit_of_measure uom,
mmt.subinventory_code,
msi.description,
msi.inventory_item_id,
msi.organization_id, cic.item_cost,
SUM
(mmt.primary_quantity
) on_hand_quantity,
( SUM (mmt.primary_quantity)
* cic.item_cost
) total
FROM mtl_system_items_kfv msi,
mtl_material_transactions mmt,
cst_item_costs cic,
org_organization_definitions ood,
hr_operating_units hou
WHERE msi.inventory_item_id =
mmt.inventory_item_id
AND msi.organization_id =
mmt.organization_id
AND cic.inventory_item_id =
msi.inventory_item_id
AND cic.organization_id =
msi.organization_id
AND cic.cost_type_id = 2
AND mmt.subinventory_code IS NOT NULL
AND ood.organization_id =
mmt.organization_id
AND ood.operating_unit =
hou.organization_id
AND hou.organization_id =
NVL (:p_operation_unit,
hou.organization_id
)
AND msi.organization_id =
NVL (:p_organia1ation,
msi.organization_id
)
AND mmt.transaction_action_id NOT IN
(24, 30)
AND mmt.transaction_type_id NOT IN
(10008)
AND TRUNC (mmt.transaction_date) <=
TRUNC (:p_date)
HAVING SUM (mmt.primary_quantity) IS NOT NULL
--AND moqd.inventory_item_id = 18339
GROUP BY msi.concatenated_segments,
msi.description,
msi.inventory_item_id,
msi.organization_id,
ood.organization_name,
msi.primary_unit_of_measure,
mmt.subinventory_code,
msi.item_type,
hou.NAME,
cic.item_cost) a
--WHERE item_code = :p_item
) z))
ORDER BY subinventory_code, inventory_item_id