AMRESH KUMARAN ORACLE APPLICATIONS

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

Monday, 12 January 2015

Stock Transfer Report query

/* Formatted on 2015/01/06 18:00 (Formatter Plus v4.8.8) */
SELECT DISTINCT UPPER (hl.address_line_1)
|| CHR (10)
|| hl.address_line_2
|| ','
|| hl.address_line_3
|| ','
|| CHR (10)
|| hl.town_or_city
|| ','
|| hl.postal_code
|| CHR (10)
|| hl.telephone_number_1 from_address,
NVL (jihou.excise_duty_range, 'NOT APPLICABLE') RANGE,
NVL (jihou.excise_duty_division, 'NOT APPLICABLE') division,
NVL (jihou.pan_no, 'NOT APPLICABLE') pan_no,
NVL (jihou.excise_duty_reg_no, 'NOT APPLICABLE') regn,
NVL (jihou.ec_code, 'NOT APPLICABLE') ecc_no,
NVL (jihou.excise_duty_comm, 'NOT APPLICABLE') pla_no,
'NOT APPLICABLE' exemp,
NVL (jihou.cst_reg_no, 'NOT APPLICABLE') cst_no,
NVL (jihou.vat_reg_no, 'NOT APPLICABLE') tin_no,
ra.cust_account_id customer_id, hp.party_name customer_name,
oohv.order_number, oohv.ordered_date order_date,
oohv.flow_status_code ord_status, rtm.NAME term,
oohv.cust_po_number po_no, oohv.header_id,
oohv.attribute2 comments,
oohv.transactional_curr_code currency, oohv.conversion_rate,
oohv.invoice_to_org_id bill, oohv.ship_to_org_id ship,
oohv.orig_sys_document_ref ord_reference,
oolv.inventory_item_id, (oolv.ordered_quantity) qty,
oolv.order_quantity_uom uom, oolv.subinventory,
oolv.actual_shipment_date ship_date, mk.description item_name,
mk.concatenated_segments item_code,
((oolv.unit_selling_price) * NVL (oohv.conversion_rate, 1)
) rate,
oolv.unit_selling_price item_price, oolv.line_id,
oolv.line_number, oohv.attribute10 bill_branch,
oohv.attribute11 ship_branch,
-- decode(jt.TAX_TYPE,'VALUE ADDED TAX','VAT','EXCISE_SH_EDU_CESS','Sh_Cess','EXCISE_EDUCATION_CESS','Edu_Cess',jt.tax_type) Tax_Name,
-- jo.TAX_RATE,
-- jo.FUNC_TAX_AMOUNT Tax_Amt,
oohv.conversion_rate exch_rate,
jl.order_line_id, jl.vat_invoice_no, jl.vat_invoice_date,
ot.NAME order_type, rsa.NAME salesperson, fu.user_name,
micv.category_set_name category_name,
micv.category_id category_code,

-- jisptl.TAX_LINE_NO,
DECODE (oohv.attribute14,
'10001', 'SALE AGAINST FORM-C',
'11111', NULL,
'20001', NULL,
'90001', 'STOCK TRANSFER AGAINST FORM-F',
'90002', 'STOCK TRANSFER- WITH IN STATE',
'90003', 'CONSIGNMENT TRANSFER AGAINST FORM-F'
) form_details,
oolv.unit_list_price price_list_price,
oolv.ship_from_org_id org_id,
SUBSTR (mk.concatenated_segments, 1, 1) item_class,
(SELECT (jitc.tax_rate)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'Excise') excise_rate1,
(SELECT SUM (jptl.tax_amount)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'Excise') excise_amount1,
(SELECT (jitc.tax_rate)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'EXCISE_EDUCATION_CESS') excise_cess1,
(SELECT SUM (jptl.tax_amount)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'EXCISE_EDUCATION_CESS')
excise_cess_amount1,
(SELECT (jitc.tax_rate)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'EXCISE_SH_EDU_CESS')
excise_sec_edu_cess1,
(SELECT SUM (jptl.tax_amount)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'EXCISE_SH_EDU_CESS')
excise_sec_edu_cess_amount1,
(SELECT SUM (d.tax_amount)
FROM jai_om_oe_so_taxes d
WHERE d.header_id = oohv.header_id
AND d.line_id = oolv.line_id) tax_amt,
(SELECT (jitc.tax_rate)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'VALUE ADDED TAX') value_added_tax1,
(SELECT SUM (jptl.tax_amount)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'VALUE ADDED TAX')
value_added_tax_amount1,
(SELECT (jitc.tax_rate)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'Freight') freight,
(SELECT SUM (jptl.tax_amount)
FROM jai_om_oe_so_taxes jptl,
jai_cmn_taxes_all jitc
WHERE jptl.tax_id = jitc.tax_id
AND jptl.header_id = jisptl.header_id
AND jptl.line_id = jisptl.line_id
AND jitc.tax_type = 'Freight') freight_amount1,
jisptl.header_id, hp.party_id, oohv.org_id,
jisptl.line_id inv_line, oolv.price_list_id,
oolv.ordered_quantity receipt_qty,
-- (oolv.unit_selling_price * oolv.ordered_quantity
-- ) receipt_value,
rsh.ship_to_org_id, rsl.mmt_transaction_id rcv_transction,
rt.transaction_id rt_transct, rsl.requisition_line_id , mmt.transaction_id mater_tranc_id ,
(select BASE_TRANSACTION_VALUE from cst_inv_distribution_v where transaction_id = mmt.transaction_id and BASE_TRANSACTION_VALUE >= 0) receipt_value
-- (select BASE_TRANSACTION_VALUE from mtl_transaction_accounts where TRANSACTION_ID= rsl.mmt_transaction_id
-- and organization_id = rsh.SHIP_TO_ORG_ID and BASE_TRANSACTION_VALUE >= 0) valuess
FROM ra_terms rtm,
po_requisition_headers_all prha,
po_requisition_lines_all prla,
rcv_shipment_lines rsl,
rcv_shipment_headers rsh,
rcv_transactions rt, mtl_material_transactions mmt ,
oe_order_headers_all oohv,
oe_transaction_types_tl ot,
ra_salesreps_all rsa,
oe_order_lines_all oolv,
hz_cust_accounts ra,
jai_cmn_inventory_orgs jihou,
jai_om_wsh_lines_all jl,
jai_om_oe_so_taxes jisptl,
jai_cmn_taxes_all jt,
hr_locations hl,
mtl_system_items_kfv mk,
mtl_item_categories_v micv,
fnd_user fu,
hz_parties hp
WHERE rtm.term_id(+) = oohv.payment_term_id and mmt.RCV_TRANSACTION_ID = rt.transaction_id
AND oohv.orig_sys_document_ref = prha.segment1
AND prha.requisition_header_id = prla.requisition_header_id
AND rsl.requisition_line_id = prla.requisition_line_id
AND rsh.shipment_header_id = rsl.shipment_header_id
AND mk.inventory_item_id = prla.item_id
AND oohv.order_type_id = ot.transaction_type_id
AND rsh.receipt_source_code = 'INTERNAL ORDER'
AND rt.shipment_header_id = rsl.shipment_header_id
AND rsa.salesrep_id(+) = oohv.salesrep_id
AND rt.shipment_line_id = rsl.shipment_line_id
AND rt.transaction_type = 'DELIVER'
AND jl.order_header_id = oohv.header_id
AND jl.order_line_id = oolv.line_id
AND ra.party_id = hp.party_id
AND oolv.inventory_item_id = mk.inventory_item_id
AND oolv.ship_from_org_id = mk.organization_id
AND micv.organization_id = mk.organization_id
AND micv.inventory_item_id = mk.inventory_item_id
AND oohv.created_by = fu.user_id
AND jisptl.line_id(+) = oolv.line_id
AND jisptl.tax_id = jt.tax_id(+)
AND oohv.sold_to_org_id = ra.cust_account_id
AND oolv.header_id = oohv.header_id
AND oolv.org_id = oohv.org_id
AND hl.inventory_organization_id = oolv.ship_from_org_id
AND oolv.ship_from_org_id = jihou.organization_id
AND jihou.location_id = hl.location_id
AND ot.NAME LIKE '%INTERNAL%'
AND jl.vat_invoice_no BETWEEN NVL (:from_order, jl.vat_invoice_no)
AND NVL (:to_order, jl.vat_invoice_no)
AND oolv.inventory_item_id = NVL (:item, oolv.inventory_item_id)
AND SUBSTR (mk.concatenated_segments, 1, 1) =
NVL (:item_class, SUBSTR (mk.concatenated_segments, 1, 1))
AND micv.category_id = NVL (:item_category, micv.category_id)
AND jl.vat_invoice_date >= :p_from
AND jl.vat_invoice_date <= :p_to + 1
-- and oohv.order_number=nvl(:Order_No,oohv.order_number)
AND oohv.org_id = NVL (:p_org_id, oohv.org_id)
AND oolv.ship_from_org_id =
NVL (:p_organization_id, oolv.ship_from_org_id)
AND hl.location_id =
DECODE ((SELECT hl1.location_id
FROM hr_locations hl1
WHERE 1 = 1
AND hl1.inventory_organization_id =
oolv.ship_from_org_id
AND hl1.inactive_date > oohv.ordered_date),
NULL, (SELECT hl1.location_id
FROM hr_locations hl1
WHERE 1 = 1
AND hl1.inventory_organization_id =
oolv.ship_from_org_id
AND hl1.inactive_date IS NULL),
(SELECT hl1.location_id
FROM hr_locations hl1
WHERE 1 = 1
AND hl1.inventory_organization_id =
oolv.ship_from_org_id
AND hl1.inactive_date > oohv.ordered_date)
)
--AND oohv.ORDER_NUMBER= '4380206968'
GROUP BY oohv.order_number,
oohv.ordered_date,
oolv.inventory_item_id,
jl.order_line_id,
oolv.line_id,
oolv.payment_term_id,
oolv.unit_selling_price,
oohv.flow_status_code,
ra.cust_account_id,
hp.party_name,
hp.party_id,
oohv.cust_po_number,
oolv.order_quantity_uom,
oolv.ordered_quantity,
oolv.ship_from_org_id,
fu.user_name,
jl.vat_invoice_no,
oohv.transactional_curr_code,
oohv.orig_sys_document_ref,
oohv.invoice_to_org_id,
jl.vat_invoice_date,
oohv.ship_to_org_id,
oohv.header_id,
oohv.attribute2,
ot.NAME,
rsa.NAME,
oohv.attribute10,
oohv.attribute11,
micv.category_set_name,
micv.category_id,
oohv.org_id,
oolv.subinventory,
hp.city,
oolv.actual_shipment_date,
mk.description,
mk.concatenated_segments,
hp.state,
oohv.attribute14,
oolv.unit_list_price,
-- rt.name,
oohv.conversion_rate,
oolv.price_list_id,
jihou.excise_duty_range,
jihou.excise_duty_division,
jihou.pan_no,
jihou.excise_duty_reg_no,
jihou.ec_code,
jihou.excise_duty_comm,
jihou.cst_reg_no,
jihou.excise_duty_comm,
jihou.vat_reg_no,
-- oolv.ORG_ID,
hl.location_id,
hl.ship_to_location_id,
rtm.NAME,
hl.address_line_1,
hl.address_line_2,
hl.address_line_3,
hl.town_or_city,
hl.postal_code,
hl.telephone_number_1,
jisptl.header_id,
jisptl.line_id,
oolv.line_number,
rsh.ship_to_org_id,
rsl.mmt_transaction_id,
rt.transaction_id,
rsl.requisition_line_id , mmt.transaction_id
-- jisptl.TAX_LINE_NO,
-- jt.tax_type
-- jo.TAX_LINE_NO,
-- jo.TAX_RATE,
-- jo.FUNC_TAX_AMOUNT
ORDER BY oohv.order_number, oolv.line_number
-- jisptl.TAX_LINE_NO

Monday, 29 December 2014

Trial Balance Re- Conciliation Query

CREATE OR REPLACE PROCEDURE APPS.trial_balance (
i_fromdate DATE,
i_todate DATE,
i_acct_code NUMBER,
acct_code7 NUMBER,
i_org_code NUMBER
)
IS
/*#########################################################################################
# Module: GENERAL LEDGER #
# #
# File name: TRAIL_BALANCE #
# #
# Function: PROCEDURE to hold the Recipes Details #
# #
# Author: AMRESH KUMARAN #
# #
# Written: 28-DEC-2014 #
# #
# Modification History #
#-----------------------------------------------------------------------------------------#
# Author Date Version Description #
#-----------------------------------------------------------------------------------------#
# AMRESH KUMARAN 28-DEC-2014 1.0 Initial Version #
#########################################################################################*/
BEGIN
INSERT INTO trial_balance_table
(acct_code, je_source, je_category, supplier_code,
supplier_name, NAME, description, batch_name, journal_name,
journal_description, jv_date, voucher_no, voucher_date,
po_number, po_date, narration, debit_amount, credit_amount)
SELECT acct_code, je_source, je_category, supplier_code, supplier_name,
NAME, description, batch_name, journal_name,
journal_description, jv_date, voucher_no, voucher_date,
po_number, po_date, narration, debit_amount, credit_amount
FROM (SELECT DISTINCT ord, acct AS acct_code, je_source, je_category,
supplier_code, supplier_name, NAME, description,
(CASE
WHEN doc_sequence_value IS NOT NULL
THEN doc_sequence_value
ELSE NULL
END
) AS doc_sequence_value,
batch_name, journal_name, journal_description,
jv_date,
(CASE
WHEN user_name IS NOT NULL
THEN user_name
ELSE NULL
END
) AS user_name,
(CASE
WHEN created_by IS NOT NULL
THEN created_by
ELSE 0
END
) AS created_by,
transaction_number AS voucher_no,
transaction_date AS voucher_date,
ctransaction_number AS chq_no,
ctransaction_date AS chq_date,
(CASE
WHEN po_number IS NOT NULL
THEN po_number
ELSE NULL
END
) AS po_number,
(CASE
WHEN po_date IS NOT NULL
THEN po_date
ELSE NULL
END
) AS po_date,
(CASE
WHEN po_discription IS NOT NULL
THEN po_discription
ELSE NULL
END
) AS narration,
(CASE
WHEN fpost_adamount IS NOT NULL
THEN fpost_adamount
ELSE 0
END
) AS debit_amount,
(CASE
WHEN fpost_acamount IS NOT NULL
THEN fpost_acamount
ELSE 0
END
) AS credit_amount,
(CASE
WHEN fpost_adamount IS NOT NULL
THEN fpost_adamount
ELSE 0
END
) AS jfpost_adamount,
(CASE
WHEN fpost_acamount IS NOT NULL
THEN fpost_acamount
ELSE 0
END
) AS jfpost_acamount,
je_header_id, je_line_num, gl_sl_link_id
FROM (SELECT DISTINCT 1 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source,
(CASE
WHEN rct.destination_type_code IN
('EXPENSE')
THEN 'Receiving'
WHEN rct.destination_type_code IN
('RECEIVING')
THEN 'Inventory'
END
) AS je_category,
gjh.NAME, gjh.description,
gir.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
receipt_num
AS transaction_number,
'' AS ctransaction_number,
TO_CHAR
(rct.transaction_date,
'DD-MON-YYYY'
) AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
xal.accounted_dr
AS fpost_adamount,
xal.accounted_cr
AS fpost_acamount,
(CASE
WHEN CAST
(gir.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS jfpost_adamount,
(CASE
WHEN CAST
(gir.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS jfpost_acamount,
gir.je_header_id,
gir.je_line_num,
xal.gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
aps.segment1 supplier_code,
aps.vendor_name supplier_name
FROM apps.po_headers_all pha,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.rcv_transactions rct,
apps.rcv_shipment_headers rsh,
apps.xla_transaction_entities_upg xte,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_je_batches_headers_v glv,
apps.gl_code_combinations gcc,
apps.gl_je_lines gjl
WHERE TRUNC (xal.accounting_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
AND rct.po_header_id =
pha.po_header_id
AND rct.transaction_type IN
('DELIVER', 'RECEIVE',
'CORRECT')
AND rct.destination_type_code IN
('EXPENSE', 'RECEIVING')
AND pha.vendor_id = aps.vendor_id
AND pha.vendor_site_id =
apss.vendor_site_id
AND apss.vendor_id = aps.vendor_id
AND apss.vendor_id = pha.vendor_id
AND xte.entity_code =
'RCV_ACCOUNTING_EVENTS'
AND rsh.shipment_header_id =
rct.shipment_header_id
AND rct.transaction_id =
xte.source_id_int_1
AND xah.entity_id = xte.entity_id
AND xah.ae_header_id =
xal.ae_header_id
AND gir.gl_sl_link_id =
xal.gl_sl_link_id
AND gir.je_header_id =
lv.je_header_id
AND gir.je_line_num =
lv.je_line_num
AND gir.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gir.je_header_id =
glv.je_header_id
AND gir.je_batch_id =
glv.je_batch_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gir.je_header_id =
gjl.je_header_id
AND gcc.code_combination_id =
gjl.code_combination_id
UNION ALL
SELECT DISTINCT 2 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
'' AS transaction_number,
'' AS ctransaction_number,
'' AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS fpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS fpost_acamount,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS jfpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(CASE
WHEN gg.gl_sl_link_id IS NOT NULL
THEN gg.gl_sl_link_id
ELSE 0
END
) AS gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
NULL supplier_code,
NULL supplier_name
FROM apps.gl_import_references gg,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE gg.je_header_id =
lv.je_header_id
AND gg.je_line_num = lv.je_line_num
AND gg.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gg.je_header_id =
glv.je_header_id
AND gg.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN ('Inventory')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
UNION ALL
SELECT DISTINCT 3 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
gg.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
(CASE
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT rsh.receipt_num
FROM rcv_shipment_headers rsh,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rsh.shipment_header_id =
rt.shipment_header_id)
ELSE (SELECT rsh.receipt_num
FROM rcv_transactions rt,
rcv_shipment_headers rsh
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND rt.shipment_header_id =
rsh.shipment_header_id)
END
)
ELSE (SELECT aia.invoice_num
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
END
) AS transaction_number,
(SELECT TO_CHAR
(check_number
)
AS invoice_num
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_checks_all aca,
apps.ap_invoice_payments_all aipa
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aca.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aipa.check_id =
aca.check_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS ctransaction_number,
(CASE
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT TO_CHAR
(rsh.creation_date,
'DD-MON-YYYY'
)
FROM rcv_shipment_headers rsh,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rsh.shipment_header_id =
rt.shipment_header_id)
ELSE (SELECT TO_CHAR
(rsh.creation_date,
'DD-MON-YYYY'
)
FROM rcv_transactions rt,
rcv_shipment_headers rsh
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND rt.shipment_header_id =
rsh.shipment_header_id)
END
)
ELSE (SELECT TO_CHAR
(aia.invoice_date,
'DD-MON-YYYY'
)
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
END
) AS transaction_date,
(SELECT TO_CHAR
(aca.check_date,
'DD-MON-YYYY'
)
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_checks_all aca,
apps.ap_invoice_payments_all aipa
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aca.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aipa.check_id =
aca.check_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS ctransaction_date,
(SELECT pha.segment1
AS po_number
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aial,
apps.po_headers_all pha
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND aia.invoice_id =
aial.invoice_id
AND aial.po_header_id =
pha.po_header_id
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND gcc.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS po_number,
(SELECT TO_CHAR
(pha.creation_date,
'DD-MON-YYYY'
)
AS po_date
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aial,
apps.po_headers_all pha
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aia.invoice_id =
aial.invoice_id
AND aial.po_header_id =
pha.po_header_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND gcc.segment1 =
i_org_code
AND h.status = 'P')
AS po_date,
(SELECT xal.description
|| ' - '
|| sm.vendor_name
AS po_discription
FROM apps.gl_import_references gir,
apps.gl_je_headers h,
apps.gl_code_combinations g,
xla_ae_lines xal,
apps.gl_je_lines_v l,
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aial,
apps.ap_suppliers sm
WHERE h.je_header_id =
gir.je_header_id
AND gir.je_header_id =
l.je_header_id
AND xal.gl_sl_link_id =
gir.gl_sl_link_id
AND xal.gl_sl_link_id =
gg.gl_sl_link_id
AND l.code_combination_id =
g.code_combination_id
AND aia.doc_sequence_value =
gir.subledger_doc_sequence_value
AND aia.invoice_id =
aial.invoice_id
AND h.je_header_id =
gjh.je_header_id
AND l.je_line_num =
lv.je_line_num
AND aia.vendor_id =
sm.vendor_id
AND ROWNUM = 1
AND TRUNC
(h.default_effective_date
) BETWEEN i_fromdate
AND i_todate
AND g.segment3 =
i_acct_code
AND g.segment7 =
acct_code7
AND g.segment1 =
i_org_code
AND h.status = 'P')
AS po_discription,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS fpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS fpost_acamount,
(CASE
WHEN CAST
(gg.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
) AS jfpost_adamount,
(CASE
WHEN CAST
(gg.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gg.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num, gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
(CASE
WHEN gjh.je_category =
'Payments'
THEN
--(case when (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 1)))='Payment Matured' THEN (select aps.segment1 from ap_checks_all aca,ap_suppliers aps where aps.vendor_id=aca.vendor_id and aca.check_number=(trim(REGEXP_SUBSTR (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 2)),'[^]+', 1, 2))) AND ROWNUM=1) ELSE
(SELECT aps.segment1
FROM ap_invoice_payments_all apa,
apps.ap_invoices_all aia,
ap_suppliers aps
WHERE apa.invoice_id =
aia.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND accounting_event_id =
gg.reference_6
AND ROWNUM = 1)
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT pv.segment1
FROM po_vendors pv,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rt.vendor_id =
pv.vendor_id)
ELSE (SELECT pv.segment1
FROM rcv_transactions rt,
po_vendors pv
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND pv.vendor_id =
rt.vendor_id)
END
)
WHEN gjh.je_category =
'Purchase Invoices'
THEN (SELECT aps.segment1
FROM apps.ap_invoices_all aia,
ap_invoice_distributions_all aid,
ap_suppliers aps
WHERE accounting_event_id =
gg.reference_6
AND aia.invoice_id =
aid.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND ROWNUM = 1)
ELSE NULL
END
) supplier_code,
(CASE
WHEN gjh.je_category =
'Payments'
THEN
--(case when (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 1)))='Payment Matured' THEN (select aps.vendor_name from ap_checks_all aca,ap_suppliers aps where aps.vendor_id=aca.vendor_id and aca.check_number=(trim(REGEXP_SUBSTR (trim(REGEXP_SUBSTR (GJH.DESCRIPTION, '[^,]+', 1, 2)),'[^]+', 1, 2))) AND ROWNUM=1)ELSE
(SELECT aps.vendor_name
FROM ap_invoice_payments_all apa,
apps.ap_invoices_all aia,
ap_suppliers aps
WHERE apa.invoice_id =
aia.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND accounting_event_id =
gg.reference_6
AND ROWNUM = 1)
WHEN gjh.je_category =
'Receiving India'
THEN (CASE
WHEN gg.reference_4 =
'jai_rgm_trx_records'
THEN (SELECT pv.vendor_name
FROM po_vendors pv,
jai_rgm_trx_records jrt,
rcv_transactions rt
WHERE repository_id =
gg.reference_6
AND rt.transaction_id =
jrt.source_document_id
AND rt.vendor_id =
pv.vendor_id)
ELSE (SELECT pv.vendor_name
FROM rcv_transactions rt,
po_vendors pv
WHERE TO_CHAR
(transaction_id
) =
gg.reference_5
AND pv.vendor_id =
rt.vendor_id)
END
)
WHEN gjh.je_category =
'Purchase Invoices'
THEN (SELECT aps.vendor_name
FROM apps.ap_invoices_all aia,
ap_invoice_distributions_all aid,
ap_suppliers aps
WHERE accounting_event_id =
gg.reference_6
AND aia.invoice_id =
aid.invoice_id
AND aps.vendor_id =
aia.vendor_id
AND ROWNUM = 1)
ELSE NULL
END
) supplier_name
FROM apps.gl_import_references gg,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE gg.je_header_id =
lv.je_header_id
AND gg.je_line_num = lv.je_line_num
AND gg.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gg.je_header_id =
glv.je_header_id
AND gg.je_batch_id =
glv.je_batch_id
AND gjh.je_source IN
('Purchasing India',
'Payables')
AND gjh.status = 'P'
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 = acct_code7
AND gcc.segment1 = i_org_code
UNION ALL
SELECT DISTINCT 4 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
gg.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
(SELECT trx_number
FROM apps.ra_customer_trx_all rcta
WHERE rcta.trx_number =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1)
AS transaction_number,
'' AS ctransaction_number,
(SELECT TO_CHAR
(trx_date,
'DD-MON-YYYY'
)
FROM apps.ra_customer_trx_all rcta
WHERE rcta.trx_number =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1)
AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,

--(Case When GG.REFERENCE_9 Is Not Null Then Cast(GG.REFERENCE_9 as Number) Else 0 End) as FPOST_ADAmount,
--(Case When GG.REFERENCE_10 Is Not Null Then Cast(GG.REFERENCE_10 as Number) Else 0 End) as FPOST_ACAmount,
--(Case When GG.REFERENCE_9 Is Not Null Then Cast(GG.REFERENCE_9 as Number) Else 0 End) as JFPOST_ADAmount,
--(Case When GG.REFERENCE_10 Is Not Null Then Cast(GG.REFERENCE_10 as Number) Else 0 End) as JFPOST_ACAmount,
lv.entered_dr
AS fpost_adamount,
lv.entered_cr
AS fpost_acamount,
lv.accounted_dr
AS jfpost_adamount,
lv.accounted_cr
AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
gg.gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
(CASE
WHEN gjh.je_category =
'Register India'
THEN (CASE
WHEN gg.reference_4 =
'RA_CUSTOMER_TRX_ALL'
THEN (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_line_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
END
)
--when gjh.je_category='India Tax Collected' then (SELECT ac.customer_name FROM wsh_new_deliveries wsh, apps.ar_customers ac where wsh.customer_id=ac.customer_id and wsh.delivery_id=gg.REFERENCE_5)
WHEN gjh.je_category =
'India Tax Collected'
THEN (CASE
WHEN gg.reference_4 =
'ra_customer_trx_all'
THEN (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_number
FROM wsh_new_deliveries wsh,
apps.ar_customers ac
WHERE wsh.customer_id =
ac.customer_id
AND wsh.delivery_id =
gg.reference_5)
END
)
WHEN gjh.je_category =
'Receipts'
THEN (SELECT ac.customer_number
FROM ar_cash_receipts_all arc,
ar_customers ac
WHERE arc.doc_sequence_value =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1
AND ac.customer_id =
arc.pay_from_customer)
WHEN gjh.je_category =
'Adjustment'
THEN (SELECT ac.customer_number
FROM ar_adjustments_all adj,
ra_customer_trx_all rcta,
ar_customers ac
WHERE adj.event_id =
gg.reference_6
AND rcta.customer_trx_id =
adj.customer_trx_id
AND ac.customer_id =
rcta.bill_to_customer_id
AND ROWNUM = 1)
WHEN gjh.je_category =
'Sales Invoices'
THEN (SELECT ac.customer_number
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctd
WHERE 1 = 1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
event_id
AND ROWNUM = 1
AND rctd.customer_trx_line_id =
rctla.customer_trx_line_id
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE NULL
END
) customer_number,
(CASE
WHEN gjh.je_category =
'Register India'
THEN (CASE
WHEN gg.reference_4 =
'RA_CUSTOMER_TRX_ALL'
THEN (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_line_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
END
)
WHEN gjh.je_category =
'India Tax Collected'
THEN (CASE
WHEN gg.reference_4 =
'ra_customer_trx_all'
THEN (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla
WHERE 1 =
1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_5 =
rctla.customer_trx_id
AND ROWNUM =
1
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE (SELECT ac.customer_name
FROM wsh_new_deliveries wsh,
apps.ar_customers ac
WHERE wsh.customer_id =
ac.customer_id
AND wsh.delivery_id =
gg.reference_5)
END
)
WHEN gjh.je_category =
'Receipts'
THEN (SELECT ac.customer_name
FROM ar_cash_receipts_all arc,
ar_customers ac
WHERE arc.doc_sequence_value =
CAST
(gg.subledger_doc_sequence_value AS VARCHAR2 (50)
)
AND ROWNUM = 1
AND ac.customer_id =
arc.pay_from_customer)
WHEN gjh.je_category =
'Adjustment'
THEN (SELECT ac.customer_name
FROM ar_adjustments_all adj,
ra_customer_trx_all rcta,
ar_customers ac
WHERE adj.event_id =
gg.reference_6
AND rcta.customer_trx_id =
adj.customer_trx_id
AND ac.customer_id =
rcta.bill_to_customer_id
AND ROWNUM = 1)
WHEN gjh.je_category =
'Sales Invoices'
THEN (SELECT ac.customer_name
FROM apps.ra_customer_trx_all rcta,
apps.ar_customers ac,
apps.ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctd
WHERE 1 = 1
AND rcta.customer_trx_id =
rctla.customer_trx_id
AND gg.reference_6 =
event_id
AND ROWNUM = 1
AND rctd.customer_trx_line_id =
rctla.customer_trx_line_id
AND ac.customer_id =
rcta.bill_to_customer_id)
ELSE NULL
END
) customer_name
FROM apps.gl_import_references gg,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE gg.je_header_id =
lv.je_header_id
AND gg.je_line_num = lv.je_line_num
AND gg.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gg.je_header_id =
glv.je_header_id
AND gg.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN
('Receivables',
'Receivables India')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
UNION ALL
SELECT DISTINCT 5 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
'' AS transaction_number,
'' AS ctransaction_number,
'' AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(SELECT a.gl_sl_link_id
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 = i_acct_code
AND segment7 = acct_code7
AND ROWNUM = 1)
gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
NULL supplier_code,
NULL supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num = lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN
('Cost Management')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code
AND (SELECT a.je_line_num
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 = i_acct_code
AND segment7 = acct_code7
AND ROWNUM = 1) IS NULL
UNION ALL
---- With Gl_Sl_Link_Id Start
SELECT DISTINCT ord, acct_code, je_source,
je_category, NAME, description,
doc_sequence_value, batch_name,
journal_name,
journal_description, jv_date,
user_name, created_by,
transaction_number,
ctransaction_number,
transaction_date,
ctransaction_date, po_number,
po_date, po_discription,
fpost_adamount, fpost_acamount,
jfpost_adamount,
jfpost_acamount, je_header_id,
je_line_num, gl_sl_link_id,
acct, supplier_code,
supplier_name
FROM (SELECT DISTINCT 6 AS ord,
gcc.segment3
AS acct_code,
gjh.je_source,
gjh.je_category,
gjh.NAME,
gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME
AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
)
AS jv_date,
''
AS user_name,
gjh.created_by,
''
AS transaction_number,
''
AS ctransaction_number,
''
AS transaction_date,
''
AS ctransaction_date,
''
AS po_number,
'' AS po_date,
''
AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(SELECT a.gl_sl_link_id
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 =
i_acct_code
AND segment7 =
acct_code7
AND ROWNUM =
1)
gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
acct,
(SELECT pv.segment1
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_code,
(SELECT pv.vendor_name
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num =
lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status =
'P'
AND gjh.je_source IN
('Manual',
'AutoCopy',
'Service Tax India',
'Cost Management',
'VAT India',
'Register India',
'Projects')
AND TRUNC
(gjh.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL
(i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL
(acct_code7,
gcc.segment7
)
AND gcc.segment1 =
i_org_code)
--Restrict Gl_Sl_Link_Id Start;
WHERE gl_sl_link_id NOT IN (
SELECT gl_sl_link_id
FROM (SELECT DISTINCT 1
AS ord,
gcc.segment3
AS acct_code,
gjh.je_source,
(CASE
WHEN rct.destination_type_code IN
('EXPENSE')
THEN 'Receiving'
WHEN rct.destination_type_code IN
('RECEIVING')
THEN 'Inventory'
END
)
AS je_category,
gjh.NAME,
gjh.description,
gir.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME
AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
)
AS jv_date,
''
AS user_name,
gjh.created_by,
receipt_num
AS transaction_number,
''
AS ctransaction_number,
TO_CHAR
(rct.transaction_date,
'DD-MON-YYYY'
)
AS transaction_date,
''
AS ctransaction_date,
''
AS po_number,
''
AS po_date,
''
AS po_discription,
xal.accounted_dr
AS fpost_adamount,
xal.accounted_cr
AS fpost_acamount,
(CASE
WHEN CAST
(gir.reference_9 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_9 AS NUMBER
)
ELSE lv.accounted_dr
END
)
AS jfpost_adamount,
(CASE
WHEN CAST
(gir.reference_10 AS NUMBER
) IS NOT NULL
THEN CAST
(gir.reference_10 AS NUMBER
)
ELSE lv.accounted_cr
END
)
AS jfpost_acamount,
gir.je_header_id,
gir.je_line_num,
xal.gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
acct
FROM apps.po_headers_all pha,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.rcv_transactions rct,
apps.rcv_shipment_headers rsh,
apps.xla_transaction_entities_upg xte,
apps.xla_ae_headers xah,
apps.xla_ae_lines xal,
apps.gl_import_references gir,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_je_batches_headers_v glv,
apps.gl_code_combinations gcc,
apps.gl_je_lines gjl
WHERE TRUNC
(xal.accounting_date
)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL
(i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL
(acct_code7,
gcc.segment7
)
AND gcc.segment1 =
i_org_code
AND rct.po_header_id =
pha.po_header_id
AND rct.transaction_type IN
('DELIVER',
'RECEIVE',
'CORRECT')
AND rct.destination_type_code IN
('EXPENSE',
'RECEIVING')
AND pha.vendor_id =
aps.vendor_id
AND pha.vendor_site_id =
apss.vendor_site_id
AND apss.vendor_id =
aps.vendor_id
AND apss.vendor_id =
pha.vendor_id
AND xte.entity_code =
'RCV_ACCOUNTING_EVENTS'
AND rsh.shipment_header_id =
rct.shipment_header_id
AND rct.transaction_id =
xte.source_id_int_1
AND xah.entity_id =
xte.entity_id
AND xah.ae_header_id =
xal.ae_header_id
AND gir.gl_sl_link_id =
xal.gl_sl_link_id
AND gir.je_header_id =
lv.je_header_id
AND gir.je_line_num =
lv.je_line_num
AND gir.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gir.je_header_id =
glv.je_header_id
AND gir.je_batch_id =
glv.je_batch_id
AND gcc.code_combination_id =
lv.code_combination_id
AND gir.je_header_id =
gjl.je_header_id
AND gcc.code_combination_id =
gjl.code_combination_id))
--Restrict Gl_Sl_Link_Id End
---- With Gl_Sl_Link_Id ENd
UNION ALL
---- With Gl_Sl_Link_Id Is Null Start
SELECT DISTINCT ord, acct_code, je_source,
je_category, NAME, description,
doc_sequence_value, batch_name,
journal_name,
journal_description, jv_date,
user_name, created_by,
transaction_number,
ctransaction_number,
transaction_date,
ctransaction_date, po_number,
po_date, po_discription,
fpost_adamount, fpost_acamount,
jfpost_adamount,
jfpost_acamount, je_header_id,
je_line_num, gl_sl_link_id,
acct, supplier_code,
supplier_name
FROM (SELECT DISTINCT 6 AS ord,
gcc.segment3
AS acct_code,
gjh.je_source,
gjh.je_category,
gjh.NAME,
gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME
AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
)
AS jv_date,
''
AS user_name,
gjh.created_by,
''
AS transaction_number,
(CASE
WHEN gjh.je_category =
'Receiving'
THEN (SELECT rrs.reference4
FROM xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
rcv_receiving_sub_ledger rrs,
gl_import_references gg
WHERE xah.ae_header_id =
gg.reference_7
AND gg.je_header_id =
l.je_header_id
AND gg.je_line_num =
l.je_line_num
AND xah.ae_header_id =
xal.ae_header_id
AND xdl.ae_header_id =
xal.ae_header_id
AND xdl.ae_line_num =
xal.ae_line_num
AND xdl.source_distribution_id_num_1 =
rrs.rcv_sub_ledger_id
AND xal.gl_sl_link_id =
l.gl_sl_link_id
AND ROWNUM =
1)
WHEN gjh.je_category =
'Inventory'
THEN (SELECT rsh.receipt_num
FROM mtl_transaction_accounts mta,
mtl_material_transactions mmt,
xla_ae_lines xal,
xla_distribution_links xld,
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE xal.gl_sl_link_id =
l.gl_sl_link_id
AND xal.ae_header_id =
xld.ae_header_id
AND xal.ae_line_num =
xld.ae_line_num
AND mta.transaction_id =
mmt.transaction_id
AND mta.inv_sub_ledger_id =
source_distribution_id_num_1
AND mmt.rcv_transaction_id =
rt.transaction_id
AND rt.shipment_header_id =
rsh.shipment_header_id)
ELSE NULL
END
)
AS ctransaction_number,
''
AS transaction_date,
''
AS ctransaction_date,
''
AS po_number,
'' AS po_date,
''
AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
)
AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
)
AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num,
(SELECT a.gl_sl_link_id
FROM apps.gl_import_references a,
apps.gl_je_lines_v b,
apps.gl_je_headers c
WHERE a.je_header_id =
b.je_header_id
AND a.je_header_id =
c.je_header_id
AND a.je_line_num =
b.je_line_num
AND c.je_header_id =
lv.je_header_id
AND a.je_line_num =
lv.je_line_num
AND segment3 =
i_acct_code
AND segment7 =
acct_code7
AND ROWNUM =
1)
gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7
acct,
(SELECT pv.segment1
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_code,
(SELECT pv.vendor_name
FROM ap_invoice_payments_all aip,
ap_invoices_all aia,
po_vendors pv,
gl_import_references gg
WHERE invoice_payment_id =
gg.reference_5
AND aia.invoice_id =
aip.invoice_id
AND pv.vendor_id =
aia.vendor_id
AND gg.je_header_id =
lv.je_header_id
AND gg.je_line_num =
lv.je_line_num)
supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num =
lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status =
'P'
AND gjh.je_source IN
('Manual',
'AutoCopy',
'Service Tax India',
'Cost Management',
'VAT India',
'Register India',
'21')
AND TRUNC
(gjh.default_effective_date
)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL
(i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL
(acct_code7,
gcc.segment7
)
AND gcc.segment1 =
i_org_code)
WHERE gl_sl_link_id IS NULL
---- With Gl_Sl_Link_Id Is Null Start
UNION ALL
SELECT DISTINCT 7 AS ord,
gcc.segment3 AS acct_code,
gjh.je_source, gjh.je_category,
gjh.NAME, gjh.description,
lv.subledger_doc_sequence_value
AS doc_sequence_value,
batch_name,
gjh.NAME AS journal_name,
lv.description
AS journal_description,
TRUNC
(gjh.default_effective_date
) AS jv_date,
'' AS user_name,
gjh.created_by,
'' AS transaction_number,
'' AS ctransaction_number,
'' AS transaction_date,
'' AS ctransaction_date,
'' AS po_number, '' AS po_date,
'' AS po_discription,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS fpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS fpost_acamount,
(CASE
WHEN lv.accounted_dr IS NOT NULL
THEN lv.accounted_dr
ELSE 0
END
) AS jfpost_adamount,
(CASE
WHEN lv.accounted_cr IS NOT NULL
THEN lv.accounted_cr
ELSE 0
END
) AS jfpost_acamount,
lv.je_header_id,
lv.je_line_num, gl_sl_link_id,
gcc.segment1
|| '.'
|| gcc.segment2
|| '.'
|| gcc.segment3
|| '.'
|| gcc.segment4
|| '.'
|| gcc.segment5
|| '.'
|| gcc.segment6
|| '.'
|| gcc.segment7 acct,
NULL supplier_code,
NULL supplier_name
FROM apps.gl_je_lines l,
apps.gl_je_lines_v lv,
apps.gl_je_headers gjh,
apps.gl_code_combinations gcc,
apps.gl_je_batches_headers_v glv
WHERE l.je_header_id =
lv.je_header_id
AND l.je_line_num = lv.je_line_num
AND l.je_header_id =
gjh.je_header_id
AND lv.je_header_id =
gjh.je_header_id
AND gcc.code_combination_id =
lv.code_combination_id
AND l.je_header_id =
glv.je_header_id
AND gjh.je_batch_id =
glv.je_batch_id
AND gjh.status = 'P'
AND gjh.je_source IN
('Project Accounting',
'Cash Management')
AND TRUNC
(gjh.default_effective_date)
BETWEEN i_fromdate
AND i_todate
AND gcc.segment3 =
NVL (i_acct_code,
gcc.segment3
)
AND gcc.segment7 =
NVL (acct_code7,
gcc.segment7
)
AND gcc.segment1 = i_org_code)
WHERE NVL (fpost_adamount, 0) <> 0
OR NVL (fpost_acamount, 0) <> 0
ORDER BY 15);
END;
/