The below will give all the details of the transaction in the gl account code wise..
and it will give the detail of the inventory with each and every transaction details from inventory..
/* Formatted on 5/16/2011 3:08:43 PM (QP5 v5.115.810.9015) */
SELECT /*+ INDEX (C SONA_GENERAL_LEDGER_IDX)*/
C .SEGMENT6 CC_CD,
A.JE_SOURCE,
A.JE_CATEGORY,
B.DESCRIPTION,
c.segment4 account_code,
A.NAME,
B.ACCOUNTED_DR,
B.ACCOUNTED_CR,
A.DOC_SEQUENCE_VALUE,
B.EFFECTIVE_DATE accounting_DATE,
b.JE_HEADER_ID,
B.JE_LINE_NUM,
B.REFERENCE_2 INV_ID,
B.REFERENCE_5 INV_NUM,
B.GL_SL_LINK_ID LINK_ID,
C.CODE_COMBINATION_ID,
C.SEGMENT2,
b.REFERENCE_4,
b.period_name,
a.ledger_id SET_OF_BOOKS_ID,
(SELECT NVL (SUM (GLB.begin_balance_dr - GLB.begin_balance_cr), 0)
FROM apps.gl_balances GLB
WHERE GLB.ledger_id = a.ledger_id
AND GLB.actual_flag LIKE 'A'
AND GLB.currency_code = 'INR'
AND GLB.period_name = b.period_name
AND GLB.code_combination_id IN
(SELECT glcc.code_combination_id
FROM apps.gl_code_combinations glcc
WHERE TO_NUMBER (glcc.segment4) =
TO_NUMBER (c.segment4)
AND glcc.segment2 = c.segment2))
opening_balance,
DECODE (
b.reference_4,
'rcv_transactions',
DECODE (
a.je_source,
'Purchasing India',
(SELECT DISTINCT poh.segment1
FROM apps.po_headers_all poh,
apps.rcv_shipment_lines rsl,
apps.rcv_transactions rcts
WHERE poh.po_header_id = rsl.po_header_id
AND rsl.shipment_line_id = rcts.shipment_line_id
AND rcts.transaction_id = b.reference_5
AND rcts.organization_id =
DECODE (a.ledger_id,
1, 3,
3, 24,
4, 44,
2, 45,
5, 46,
7, 65,
8, 144,
10, 165,
12, 185,
9999)),
NULL
),
NULL
)
po_number,
DECODE (
b.reference_4,
'rcv_transactions',
DECODE (
a.je_source,
'Purchasing India',
(SELECT DISTINCT pol.LINE_NUM
FROM apps.po_lines_all pol,
apps.rcv_shipment_lines rsl,
apps.rcv_transactions rcts
WHERE pol.PO_LINE_ID = rsl.PO_LINE_ID
AND rsl.shipment_line_id = rcts.shipment_line_id
AND rcts.transaction_id = b.reference_5
AND rcts.organization_id =
DECODE (a.ledger_id,
1, 3,
3, 24,
4, 44,
2, 45,
5, 46,
7, 65,
8, 144,
10, 165,
12, 185,
9999)),
NULL
),
NULL
)
po_line_num,
DECODE (
b.reference_4,
'rcv_transactions',
DECODE (
a.je_source,
'Purchasing India',
(SELECT DISTINCT pol.attribute6
FROM apps.po_lines_all pol,
apps.rcv_shipment_lines rsl,
apps.rcv_transactions rcts
WHERE pol.PO_LINE_ID = rsl.PO_LINE_ID
AND rsl.shipment_line_id = rcts.shipment_line_id
AND rcts.transaction_id = b.reference_5
AND rcts.organization_id =
DECODE (a.ledger_id,
1, 3,
3, 24,
4, 44,
2, 45,
5, 46,
7, 65,
8, 144,
10, 165,
12, 185,
9999)),
NULL
),
NULL
)
po_line_name,
(CASE
WHEN a.JE_SOURCE = 'Payables'
AND A.JE_CATEGORY = 'Purchase Invoices'
THEN
(SELECT VENDOR_NAME
FROM APPS.AP_SUPPLIERS AP, APPS.AP_INVOICES_ALL AIA
WHERE AP.VENDOR_ID = AIA.VENDOR_ID
AND AIA.DOC_SEQUENCE_VALUE =
B.SUBLEDGER_DOC_SEQUENCE_VALUE
AND AIA.DOC_SEQUENCE_ID =
B.SUBLEDGER_DOC_SEQUENCE_ID
AND ROWNUM = 1)
WHEN a.JE_SOURCE = 'Purchasing India'
AND A.JE_CATEGORY = 'Receiving India'
THEN
(SELECT VENDOR_NAME
FROM APPS.AP_SUPPLIERS AP, APPS.RCV_TRANSACTIONS AIA
WHERE AP.VENDOR_ID = AIA.VENDOR_ID
AND AIA.TRANSACTION_ID = TO_NUMBER (B.REFERENCE_5)
AND ROWNUM = 1)
ELSE
NULL
END)
vendor_name,
DECODE (
a.je_source,
'Purchasing',
(SELECT DISTINCT rsh.receipt_num
FROM apps.rcv_shipment_headers rsh,
apps.rcv_transactions rct
WHERE rsh.shipment_header_id = rct.shipment_header_id
AND rct.transaction_id = b.reference_5
AND rsh.organization_id = rct.organization_id
AND rct.organization_id =
DECODE (a.ledger_id,
1, 3,
3, 24,
4, 44,
2, 45,
5, 46,
7, 65,
8, 144,
10, 165,
12, 185,
9999)),
'Manual',
a.doc_sequence_value,
(SELECT DISTINCT glir.subledger_doc_sequence_value
FROM apps.gl_import_references glir
WHERE glir.je_header_id = a.je_header_id
AND glir.je_line_num = b.je_line_num
AND ROWNUM = 1)
)
vou_num,
-- d.start_date,
0 Quantity,
DECODE (a.ledger_id,
1, 3,
3, 24,
4, 44,
2, 45,
5, 46,
7, 65,
8, 144,
10, 165,
12, 185,
9999)
organization_id,
NULL Item_code,
(CASE
WHEN a.JE_SOURCE = 'Payables'
AND A.JE_CATEGORY = 'Purchase Invoices'
THEN
(SELECT ap.user_name
FROM APPS.fnd_user AP, APPS.AP_INVOICES_ALL AIA
WHERE AP.user_id = AIA.CREATED_BY
AND AIA.DOC_SEQUENCE_VALUE =
B.SUBLEDGER_DOC_SEQUENCE_VALUE
AND AIA.DOC_SEQUENCE_ID =
B.SUBLEDGER_DOC_SEQUENCE_ID
AND ROWNUM = 1)
WHEN a.JE_SOURCE = 'Purchasing India'
AND A.JE_CATEGORY = 'Receiving India'
THEN
(SELECT ap.user_name
FROM APPS.fnd_user AP, APPS.RCV_TRANSACTIONS AIA
WHERE AP.user_id = AIA.CREATED_BY
AND AIA.TRANSACTION_ID = TO_NUMBER (B.REFERENCE_5)
AND ROWNUM = 1)
ELSE
NULL
END)
user_name
FROM apps.GL_JE_HEADERS A,
apps.GL_JE_LINES B,
apps.GL_CODE_COMBINATIONS C
-- apps.gl_periods d
WHERE segment2 =
DECODE (a.ledger_id,
1, 'SKG',
3, 'SSL',
4, 'SPK',
2, 'SOP',
5, 'SCF',
7, 'TSS',
8, 'SKC',
10, 'EOU',
12, 'SKD',
'AAA')
AND A.JE_HEADER_ID = B.JE_HEADER_ID
AND B.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
AND A.STATUS = 'P'
AND A.ACTUAL_FLAG = 'A'
AND a.LEDGER_ID = b.LEDGER_ID
AND a.LEDGER_ID = :p_set_of_bks_id
AND TO_NUMBER (C.SEGMENT4) BETWEEN :acc_cd1 AND :acc_cd2
AND B.EFFECTIVE_DATE BETWEEN TO_DATE (:P_FROM_DATE || ' 00:00:00',
'DD-MON-YYYY HH24:MI:SS')
AND TO_DATE (:P_TO_DATE || ' 23:59:59',
'DD-MON-YYYY HH24:MI:SS')
AND TRIM (A.JE_SOURCE || A.JE_CATEGORY) NOT IN
('Cost ManagementReceiving')
AND (TRIM (A.JE_SOURCE || A.JE_CATEGORY) NOT IN
('Cost ManagementInventory')
OR b.DESCRIPTION LIKE 'Journal Import Created')
AND (TRIM (A.JE_SOURCE || A.JE_CATEGORY) NOT IN
('Cost ManagementWIP')
OR b.DESCRIPTION LIKE 'Journal Import Created')
UNION ALL
SELECT C.SEGMENT6 CC_CD,
'Cost Management' JE_SOURCE,
'Receiving' JE_CATEGORY,
rsl.ITEM_DESCRIPTION DESCRIPTION,
c.segment4 account_code,
NULL NAME,
rrsl.ACCOUNTED_DR,
rrsl.ACCOUNTED_CR,
TO_NUMBER (rsh.receipt_num) doc_sequence_value,
rrsl.accounting_DATE,
NULL JE_HEADER_ID,
NULL JE_LINE_NUM,
NULL INV_ID,
NULL INV_NUM,
NULL LINK_ID,
C.CODE_COMBINATION_ID,
C.SEGMENT2,
TO_CHAR (poh.po_header_id) REFERENCE_4,
rrsl.PERIOD_NAME,
rrsl.SET_OF_BOOKS_ID,
0 opening_balance,
poh.segment1 po_number,
(SELECT pol.line_num
FROM apps.po_lines_all pol
WHERE pol.po_header_id = rt.po_header_id
AND pol.po_line_id = rt.po_line_id)
po_line_num,
(SELECT pol.attribute6
FROM apps.po_lines_all pol
WHERE pol.po_header_id = rt.po_header_id
AND pol.po_line_id = rt.po_line_id)
po_line_name,
(SELECT DISTINCT vendor_name
FROM apps.ap_suppliers rr
WHERE rr.vendor_id = rt.VENDOR_ID)
vendor_name,
NULL vou_num,
-- d.start_date,
rt.Quantity Quantity,
rt.organization_id,
NULL ITEM_code,
(SELECT a.user_name
FROM apps.fnd_user a
WHERE a.user_id = rt.created_by)
user_name
FROM apps.RCV_RECEIVING_SUB_LEDGER rrsl,
apps.rcv_transactions rt,
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.po_headers_all poh,
apps.GL_CODE_COMBINATIONS C
-- apps.gl_periods d
WHERE rrsl.rcv_transaction_id = rt.transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND rsl.shipment_line_id = rt.shipment_line_id
AND poh.po_header_id = rt.po_header_id
-- and rrsl.period_name = :P_PERIOD_NAME
AND rrsl.ACCOUNTING_DATE BETWEEN TO_DATE (
:P_FROM_DATE || ' 00:00:00',
'DD-MON-YYYY HH24:MI:SS'
)
AND TO_DATE (
:P_TO_DATE || ' 23:59:59',
'DD-MON-YYYY HH24:MI:SS'
)
AND rrsl.SET_OF_BOOKS_ID = :p_set_of_bks_id
AND rrsl.ACTUAL_FLAG = 'A'
AND rrsl.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
AND TO_NUMBER (C.SEGMENT4) BETWEEN :acc_cd1 AND :acc_cd2
-- and C.segment2 =
-- DECODE (rrsl.SET_OF_BOOKS_ID,
-- 1, 'SKG',
-- 3, 'SSL',
-- 4, 'SPK',
-- 2, 'SOP',
-- 5, 'SCF',
-- 7, 'TSS',
-- 8, 'SKC',
-- 10, 'EOU',
-- 12, 'SKD',
-- 'AAA')
UNION ALL
SELECT /*+ index (mmt MTL_MATERIAL_TRANSACTIONS_N2)*/
gcc.segment6 cc_cd,
'INVENTORY' je_source,
NULL je_category,
msi.description,
gcc.segment4 account_code,
'MTL' || '-' || mmt.CURRENCY_CODE name,
CASE
WHEN mta.base_transaction_value >= 0
THEN
mta.base_transaction_value
ELSE
0
END
AS accounted_dr,
CASE
WHEN mta.base_transaction_value < 0
THEN
ABS (mta.base_transaction_value)
ELSE
0
END
AS accounted_cr,
0 doc_sequence_value,
TRUNC (mmt.transaction_date) accounting_DATE,
0 je_header_id,
0 je_line_num,
NULL inv_id,
NULL inv_num,
0 link_id,
gcc.code_combination_id,
gcc.segment2,
NULL reference_4,
(SELECT gp.PERIOD_NAME
FROM apps.gl_periods gp
WHERE PERIOD_SET_NAME = 'Sona Fiscal Cal'
AND ROWNUM = 1
AND gp.ADJUSTMENT_PERIOD_FLAG = 'N'
AND TRUNC (mmt.Transaction_date) BETWEEN gp.START_DATE
AND gp.END_DATE)
period_name,
DECODE (mmt.organization_id,
3, 1,
24, 3,
44, 4,
45, 2,
46, 4,
65, 7,
144, 8,
165, 10,
185, 12,
999)
SET_OF_BOOKS_ID,
0 opening_balance,
NULL po_number,
(SELECT DISTINCT b.LINE_NUMBER
FROM apps.mtl_txn_request_lines b
WHERE mmt.move_order_line_id = b.line_id
AND mmt.organization_id = B.organization_id)
po_line_num,
(SELECT DISTINCT attribute12
FROM apps.mtl_txn_request_lines b
WHERE mmt.move_order_line_id = b.line_id
AND mmt.organization_id = B.organization_id)
po_line_name,
NULL vendor_name,
(SELECT DISTINCT REQUEST_NUMBER
FROM apps.mtl_txn_request_headers a,
apps.mtl_txn_request_lines b
WHERE a.header_id = b.header_id
AND a.organization_id = b.organization_id
AND mmt.move_order_line_id = b.line_id
AND mmt.organization_id = a.organization_id)
Vou_Num,
--mmt.transaction_Date Start_Date,
mmt.primary_quantity Quantity,
mmt.organization_id,
msi.segment1 Item_Code,
(SELECT a.user_name
FROM apps.fnd_user a
WHERE a.user_id = mmt.created_by)
user_name
FROM apps.mtl_material_transactions mmt,
apps.mtl_transaction_accounts mta,
apps.mtl_system_items_b msi,
apps.gl_code_combinations gcc
WHERE mmt.TRANSACTION_ID = mta.transaction_id
AND mmt.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND mmt.ORGANIZATION_ID = msi.ORGANIZATION_ID
AND mta.REFERENCE_ACCOUNT = gcc.CODE_COMBINATION_ID
AND mmt.TRANSACTION_DATE BETWEEN TO_DATE (
:P_FROM_DATE || ' 00:00:00',
'DD-MON-YYYY HH24:MI:SS'
)
AND TO_DATE (
:P_TO_DATE || ' 23:59:59',
'DD-MON-YYYY HH24:MI:SS'
)
AND msi.organization_id = :Organization_id
AND TO_NUMBER (GCC.SEGMENT4) BETWEEN :FROM_ACC AND :TO_ACC
UNION ALL
SELECT /*+ index (wt WIP_TRANSACTIONS_N2)*/
gcc.segment6 cc_cd,
'INVENTORY' je_source,
NULL je_category,
msi.description,
gcc.segment4 account_code,
'WIP' || '-' || wt.CURRENCY_CODE name,
CASE
WHEN wta.base_transaction_value >= 0
THEN
wta.base_transaction_value
ELSE
0
END
AS accounted_dr,
CASE
WHEN wta.base_transaction_value < 0
THEN
ABS (wta.base_transaction_value)
ELSE
0
END
AS accounted_cr,
0 doc_sequence_value,
TRUNC (wt.transaction_date) accounting_DATE,
0 je_header_id,
0 je_line_num,
NULL inv_id,
NULL inv_num,
0 link_id,
gcc.code_combination_id,
gcc.segment2,
NULL reference_4,
(SELECT gp.PERIOD_NAME
FROM apps.gl_periods gp
WHERE PERIOD_SET_NAME = 'Sona Fiscal Cal'
AND ROWNUM = 1
AND gp.ADJUSTMENT_PERIOD_FLAG = 'N'
AND TRUNC (wt.Transaction_date) BETWEEN gp.START_DATE
AND gp.END_DATE)
period_name,
DECODE (Wt.organization_id,
3, 1,
24, 3,
44, 4,
45, 2,
46, 4,
65, 7,
144, 8,
165, 10,
185, 12,
999)
SET_OF_BOOKS_ID,
0 opening_balance,
NULL po_number,
NULL po_line_num,
NULL po_line_name,
NULL vendor_name,
-- (SELECT DISTINCT WIP_ENTITY_NAME
-- FROM wip_discrete_jobs_v a
-- WHERE a.WIP_ENTITY_ID = wt.WIP_ENTITY_ID
-- AND a.ORGANIZATION_ID = wt.ORGANIZATION_ID)
NULL Vou_Num,
--wt.transaction_Date Start_Date,
Wt.primary_quantity Quantity,
wt.organization_id,
msi.segment1 Item_Code,
(SELECT a.user_name
FROM apps.fnd_user a
WHERE a.user_id = wt.created_by)
user_name
FROM apps.wip_transactions wt,
apps.wip_transaction_accounts wta,
apps.mtl_system_items_b msi,
apps.gl_code_combinations gcc
WHERE wt.transaction_id = wta.transaction_id
AND wt.PRIMARY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND wt.ORGANIZATION_ID = msi.ORGANIZATION_ID
AND wta.REFERENCE_ACCOUNT = gcc.CODE_COMBINATION_ID
AND WT.TRANSACTION_DATE BETWEEN TO_DATE (
:P_FROM_DATE || ' 00:00:00',
'DD-MON-YYYY HH24:MI:SS'
)
AND TO_DATE (:P_TO_DATE || ' 23:59:59',
'DD-MON-YYYY HH24:MI:SS')
AND msi.organization_id = :Organization_id
AND TO_NUMBER (GCC.SEGMENT4) BETWEEN :FROM_ACC AND :TO_ACC
No comments:
Post a Comment