[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO AP_ALC_LEDGER_GT (
source_ledger_id,
ledger_id,
ledger_category_code,
org_id)
SELECT P_ledger_id,
P_ledger_id,
'P',
-99
FROM DUAL
UNION
SELECT ALC.source_ledger_id,
ALC.ledger_id,
'ALC',
ALC.org_id
FROM gl_alc_ledger_rships_v ALC
WHERE ALC.application_id = 200
AND ALC.relationship_enabled_flag = 'Y'
AND ALC.source_ledger_id = P_ledger_id;
SELECT XACA.accounting_class_code
BULK COLLECT
INTO acct_class_code_rec.l_acct_class_code_t
FROM xla_acct_class_assgns XACA,
xla_assignment_defns_b XAD,
xla_post_acct_progs_b XPAP
WHERE XACA.program_code = XAD.program_code
AND XACA.program_owner_code = XAD.program_owner_code
AND XAD.program_code = XPAP.program_code
AND XAD.program_owner_code = XPAP.program_owner_code
AND XPAP.program_owner_code = 'S'
AND XPAP.program_code = 'Mass Additions Create'
AND XPAP.application_id = 140
AND XACA.assignment_code = XAD.assignment_code
AND XACA.assignment_owner_code = XAD.assignment_owner_code
AND XAD.ledger_id = P_ledger_id
AND XAD.enabled_flag = 'Y';
INSERT INTO AP_ACCT_CLASS_CODE_GT (
accounting_class_code)
VALUES(acct_class_code_rec.l_acct_class_code_t(i));
INSERT INTO AP_ACCT_CLASS_CODE_GT (
accounting_class_code)
SELECT XACA.accounting_class_code
FROM xla_acct_class_assgns XACA,
xla_assignment_defns_b XAD,
xla_post_acct_progs_b XPAP
WHERE XACA.program_code = XAD.program_code
AND XACA.program_owner_code = XAD.program_owner_code
AND XAD.program_code = XPAP.program_code
AND XAD.program_owner_code = XPAP.program_owner_code
AND XPAP.program_owner_code = 'S'
AND XPAP.program_code = 'Mass Additions Create'
AND XPAP.application_id = 140
AND XACA.assignment_code = XAD.assignment_code
AND XACA.assignment_owner_code = XAD.assignment_owner_code
AND XAD.ledger_id IS NULL
AND XAD.enabled_flag = 'Y';
SELECT DECODE(NVL(sla_ledger_cash_basis_flag, 'N'), 'Y',
'Cash', 'Accrual')
INTO l_acct_method
FROM gl_sets_of_books
WHERE set_of_books_id = p_ledger_id;
SELECT count(aeh.event_id)
INTO l_count
FROM xla_ae_headers aeh, ap_invoice_payments aip
WHERE aeh.ledger_id = aip.set_of_books_id
AND aeh.application_id = 200
AND aeh.event_id = aip.accounting_event_id
AND aip.invoice_id = p_invoice_id
AND aeh.gl_transfer_status_code='N' ;
PROCEDURE Insert_Mass(
P_acctg_date IN DATE,
P_ledger_id IN NUMBER,
P_user_id IN NUMBER,
P_request_id IN NUMBER,
P_bt_code IN VARCHAR2,
P_count OUT NOCOPY NUMBER,
P_primary_accounting_method IN VARCHAR2,
P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
--
l_current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(100) := 'INSERT_MASS';
'Insert_Mass';
UPDATE /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ ap_invoice_distributions APID --Bug 14314657 added hint
SET APID.assets_addition_flag = 'N'
WHERE APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code IN ('REC_TAX','NONREC_TAX','MISCELLANEOUS','FREIGHT')
AND APID.accounting_date <= P_acctg_date --Bug 14314657
AND NVL(NVL(apid.charge_applicable_to_dist_id, apid.related_id), apid.corrected_invoice_dist_id) IS NULL
AND APID.posted_flag = 'Y'
AND apid.set_of_books_id = P_ledger_id
AND apid.assets_tracking_flag = 'N'; -- Bug 13821160 --Bug 14314657 changed <> 'Y' to = 'N'
UPDATE /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/ ap_invoice_distributions APID --Bug 14314657 added hint
SET APID.assets_addition_flag = 'N'
WHERE apid.set_of_books_id = P_ledger_id
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
AND APID.accounting_date <= P_acctg_date --Bug 14314657
AND APID.posted_flag = 'Y'
AND NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id) IS NOT NULL --Bug 14314657
AND EXISTS (SELECT /*+ push_subq no_unnest */ 'non asset ITEM' --Bug 14242750
FROM ap_invoice_distributions_all item
WHERE item.assets_tracking_flag = 'N'
AND item.invoice_distribution_id =
NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)
UNION ALL /*Bug 14242750 update aaf to N for charge dists for which related ITEM has aaf as N and atf as Y*/
SELECT /*+ push_subq no_unnest */ 'non asset ITEM'
FROM ap_invoice_distributions_all item
WHERE item.assets_tracking_flag = 'Y' AND item.assets_addition_flag = 'N'
AND item.invoice_distribution_id =
NVL(NVL(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id), apid.related_id)); -- Bug Bug 12660674
UPDATE ap_invoice_payments_all APIP
SET APIP.assets_addition_flag = 'N'
WHERE APIP.assets_addition_flag = 'U'
AND APIP.posted_flag = 'Y'
AND APIP.accounting_date <= P_acctg_date
AND APIP.set_of_books_id = P_ledger_id
AND NOT EXISTS (SELECT /*+ push_subq no_unnest */ 'Payment with discount and ATF not N'
FROM ap_payment_hist_dists APHD,
ap_invoice_distributions_all APID
WHERE APIP.accounting_event_id = APHD.accounting_event_id
AND APIP.invoice_payment_id = APHD.invoice_payment_id
AND APHD.pay_dist_lookup_code = 'DISCOUNT'
AND APHD.invoice_distribution_id = APID.invoice_distribution_id
AND (APID.asset_book_type_code = P_bt_code
OR APID.asset_book_type_code IS NULL)
AND APID.assets_addition_flag <> 'N'
AND ((APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND APID.assets_tracking_flag = 'Y')
OR EXISTS
( SELECT /*+ push_subq no_unnest */ 'X'
FROM ap_invoice_distributions_all APIDV
WHERE COALESCE(APID.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id, APID.related_id) =
APIDV.invoice_distribution_id
AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
AND APIDV.assets_tracking_flag = 'Y'
)
OR ( APID.line_type_lookup_code IN ('MISCELLANEOUS','FREIGHT','NONREC_TAX','REC_TAX')
AND APID.assets_tracking_flag = 'Y'
AND charge_applicable_to_dist_id IS NULL)
)
);
l_debug_info := 'Insert Mass if Accounting Method Is Accrual';
INSERT INTO ap_invoice_distributions_gt
(invoice_distribution_id,
invoice_id,
invoice_line_number,
po_distribution_id,
org_id,
accounting_event_id,
description,
asset_category_id,
quantity_invoiced,
historical_flag ,
corrected_quantity,
dist_code_combination_id,
line_type_lookup_code,
distribution_line_number,
accounting_date ,
corrected_invoice_dist_id,
related_id,
charge_applicable_to_dist_id,
asset_book_type_code,
set_of_books_id
)
SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
APID.invoice_distribution_id,
APID.invoice_id,
APID.invoice_line_number,
APID.po_distribution_id,
APID.org_id,
APID.accounting_event_id,
APID.description,
APID.asset_category_id,
APID.quantity_invoiced,
APID.historical_flag,
APID.corrected_quantity,
APID.dist_code_combination_id,
APID.line_type_lookup_code,
APID.distribution_line_number,
APID.accounting_date,
APID.corrected_invoice_dist_id,
APID.related_id,
APID.charge_applicable_to_dist_id,
APID.asset_book_type_code,
APID.set_of_books_id
FROM ap_invoice_distributions APID
WHERE APID.accounting_date <= P_acctg_date
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code IN ('ITEM','ACCRUAL')
AND apid.assets_tracking_flag = 'Y'
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P'
)
AND APID.posted_flag = 'Y'
AND APID.set_of_books_id = P_ledger_id
-- bug 8690407: add start
AND (APID.asset_book_type_code = P_bt_code
OR APID.asset_book_type_code IS NULL)
-- bug 8690407: add end
UNION ALL
SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
APID.invoice_distribution_id,
APID.invoice_id,
APID.invoice_line_number,
APID.po_distribution_id,
APID.org_id,
APID.accounting_event_id,
APID.description,
APID.asset_category_id,
APID.quantity_invoiced,
APID.historical_flag,
APID.corrected_quantity,
APID.dist_code_combination_id,
APID.line_type_lookup_code,
APID.distribution_line_number,
APID.accounting_date,
APID.corrected_invoice_dist_id,
APID.related_id,
APID.charge_applicable_to_dist_id,
nvl(APID.asset_book_type_code,item.asset_book_type_code),
APID.set_of_books_id
FROM ap_invoice_distributions APID,
ap_invoice_distributions_all item
WHERE APID.accounting_date <= P_acctg_date
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
AND item.assets_tracking_flag = 'Y'
AND item.assets_addition_flag IN ('Y', 'U')
AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.related_id),
apid.corrected_invoice_dist_id) IS NOT NULL
AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id),
apid.related_id) =
item.invoice_distribution_id -- Bug 12660674
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P'
)
AND APID.posted_flag = 'Y'
AND APID.set_of_books_id = P_ledger_id
-- bug 8690407: add start
AND (APID.asset_book_type_code = P_bt_code
OR APID.asset_book_type_code IS NULL)
-- bug 8690407: add end
-- bug 7215835: add start
UNION ALL
-- Bug 13821160: Add code to allow Misc/Frieight/Tax lines (not allocated) interface to FA
SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
APID.invoice_distribution_id,
APID.invoice_id,
APID.invoice_line_number,
APID.po_distribution_id,
APID.org_id,
APID.accounting_event_id,
APID.description,
APID.asset_category_id,
APID.quantity_invoiced,
APID.historical_flag,
APID.corrected_quantity,
APID.dist_code_combination_id,
APID.line_type_lookup_code,
APID.distribution_line_number,
APID.accounting_date,
APID.corrected_invoice_dist_id,
APID.related_id,
APID.charge_applicable_to_dist_id,
APID.asset_book_type_code,
APID.set_of_books_id
FROM ap_invoice_distributions_all APID
WHERE APID.accounting_date <= P_acctg_date
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT', 'NONREC_TAX','REC_TAX')
AND APID.assets_tracking_flag = 'Y'
AND NVL(APID.charge_applicable_to_dist_id,APID.corrected_invoice_dist_id) IS NULL --Bug#14495604 added in case of invoice corrections
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P'
)
AND APID.posted_flag = 'Y'
AND APID.set_of_books_id = P_ledger_id
AND (APID.asset_book_type_code = P_bt_code
OR APID.asset_book_type_code IS NULL)
UNION ALL
SELECT satx.invoice_distribution_id,
satx.invoice_id,
satx.invoice_line_number,
satx.po_distribution_id,
satx.org_id,
satx.accounting_event_id,
satx.description,
satx.asset_category_id,
satx.quantity_invoiced,
'N', -- no historical flag in self assessed table
satx.corrected_quantity,
satx.dist_code_combination_id,
satx.line_type_lookup_code,
satx.distribution_line_number,
satx.accounting_date,
satx.corrected_invoice_dist_id,
satx.related_id,
satx.charge_applicable_to_dist_id,
nvl(satx.asset_book_type_code, item.asset_book_type_code),
satx.set_of_books_id
FROM ap_invoice_distributions_all item,
ap_self_assessed_tax_dist satx
WHERE satx.accounting_date <= P_acctg_date
AND satx.assets_addition_flag = 'U'
AND item.assets_tracking_flag = 'Y'
AND item.assets_addition_flag IN ('Y', 'U')
AND satx.charge_applicable_to_dist_id IS NOT NULL
AND satx.charge_applicable_to_dist_id = item.invoice_distribution_id
AND ( satx.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = satx.project_id
) <> 'P' )
AND satx.posted_flag = 'Y'
AND satx.set_of_books_id = P_ledger_id
AND (satx.asset_book_type_code = P_bt_code OR
satx.asset_book_type_code IS NULL);
INSERT INTO FA_MASS_ADDITIONS_GT(
mass_addition_id,
asset_number,
tag_number,
description,
asset_category_id,
inventorial,
manufacturer_name,
serial_number,
model_number,
book_type_code,
date_placed_in_service,
transaction_type_code,
transaction_date,
fixed_assets_cost,
payables_units,
fixed_assets_units,
payables_code_combination_id,
expense_code_combination_id,
location_id,
assigned_to,
feeder_system_name,
create_batch_date,
create_batch_id,
last_update_date,
last_updated_by,
reviewer_comments,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
posting_status,
queue_name,
invoice_date,
invoice_created_by,
invoice_updated_by,
payables_cost,
invoice_id,
payables_batch_name,
depreciate_flag,
parent_mass_addition_id,
parent_asset_id,
split_merged_code,
ap_distribution_line_number,
post_batch_id,
add_to_asset_id,
amortize_flag,
new_master_flag,
asset_key_ccid,
asset_type,
deprn_reserve,
ytd_deprn,
beginning_nbv,
accounting_date,
created_by,
creation_date,
last_update_login,
salvage_value,
merge_invoice_number,
merge_vendor_number,
invoice_distribution_id,
invoice_line_number,
parent_invoice_dist_id,
ledger_id,
ledger_category_code,
warranty_number,
line_type_lookup_code,
po_distribution_id,
line_status
)
-- changed hint for bug 9669334
SELECT /*+ ordered use_hash(algt,aagt,polt,fsp) use_nl(pov,pod,pol,poh,xdl,xal,xah)
swap_join_inputs(algt) swap_join_inputs(fsp)
swap_join_inputs(polt) swap_join_inputs(aagt) */
NULL,
NULL,
NULL,
--bugfix:5686771 added the NVL
RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)), -- Bug#6768121
-- changed the NVL into DECODE to replace the MTLSI table for bug 9669334
DECODE(APIDG.ASSET_CATEGORY_ID , NULL,
DECODE(POL.ITEM_ID,
NULL, NULL,
(SELECT MTLSI.ASSET_CATEGORY_ID
FROM MTL_SYSTEM_ITEMS MTLSI
WHERE POL.ITEM_ID = MTLSI.INVENTORY_ITEM_ID
AND MTLSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID )),
APIDG.ASSET_CATEGORY_ID),
NULL,
APIL.manufacturer,
APIL.serial_number,
APIL.model_number,
APIDG.asset_book_type_code,
NULL,
NULL,
trunc(API.invoice_date), -- Bug 14838337
(NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
decode(APIL.match_type, /* payables_units */
'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'QTY_CORRECTION', decode(APIDG.historical_flag,
'Y',
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'PRICE_CORRECTION', decode(APIDG.historical_flag,
'Y',
1,
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'ITEM_TO_SERVICE_PO', 1,
'ITEM_TO_SERVICE_RECEIPT', 1,
'AMOUNT_CORRECTION', 1,
decode(APIDG.quantity_invoiced,
Null,1,
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1))),
decode(APIL.match_type, /* fixed_assets_units */
'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'QTY_CORRECTION', decode(APIDG.historical_flag,
'Y',
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'PRICE_CORRECTION', decode(APIDG.historical_flag,
'Y',
1,
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'ITEM_TO_SERVICE_PO', 1,
'ITEM_TO_SERVICE_RECEIPT', 1,
'AMOUNT_CORRECTION', 1,
decode(APIDG.quantity_invoiced,
Null,1,
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1))),
decode(API.source, 'Intercompany', /* payables_code_combination_id */
Inv_Fa_Interface_Pvt.Get_Ic_Ccid(
APIDG.invoice_distribution_id,
APIDG.dist_code_combination_id,
APIDG.line_type_lookup_code),
decode(APIDG.po_distribution_id, NULL,
XAL.code_combination_id,
decode(POD.accrue_on_receipt_flag, 'Y',
POD.code_combination_id,
XAL.code_combination_id)
)
),
NULL,
NULL,
POD.deliver_to_person_id,
'ORACLE PAYABLES',
SYSDATE, -- Bug 5504510
P_request_id,
SYSDATE, -- Bug 5504510
P_user_id,
NULL,
rtrim(API.invoice_num),
rtrim(POV.segment1),
API.vendor_id,
rtrim(upper(POH.segment1)),
'NEW',
'NEW',
trunc(API.invoice_date), -- Bug 14838337
API.created_by,
API.last_updated_by,
(NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
API.invoice_id,
APB.batch_name,
NULL,
NULL,
NULL,
NULL,
APIDG.distribution_line_number,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
APIDG.accounting_date,
P_user_id,
SYSDATE, -- Bug 5504510
P_user_id,
NULL,
rtrim(API.invoice_num),
rtrim(POV.segment1),
APIDG.invoice_distribution_id,
APIL.line_number,
DECODE(APIDG.line_type_lookup_code,
'ITEM', decode(APIDG.corrected_invoice_dist_id, NULL,
APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
'ACCRUAL', decode(APIDG.corrected_invoice_dist_id, NULL,
APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id), -- bug 9001504
'IPV', decode(APIDG.corrected_invoice_dist_id, NULL,
APIDG.related_id, APIDG.corrected_invoice_dist_id), -- bug 9001504, 12660674
'ERV', APIDG.related_id,
APIDG.charge_applicable_to_dist_id
),
ALGT.ledger_id,
ALGT.ledger_category_code,
APIL.warranty_number,
APIDG.line_type_lookup_code,
POD.po_distribution_id,
'NEW'
FROM ap_invoice_distributions_gt APIDG,
ap_invoice_lines_all APIL,
ap_invoices_all API,
financials_system_params_all FSP, -- changed table order # 9669334
ap_batches_all APB,
po_distributions_all POD,
po_headers_all POH,
po_lines_all POL,
po_vendors POV,
po_line_types_b POLT,
-- mtl_system_items MTLSI,
xla_distribution_links XDL,
xla_ae_lines XAL,
ap_acct_class_code_gt AAGT ,
xla_ae_headers XAH,
ap_alc_ledger_gt ALGT
WHERE APIDG.po_distribution_id = POD.po_distribution_id(+)
AND API.invoice_id = APIL.invoice_id
AND APIL.invoice_id = APIDG.invoice_id
AND APIL.line_number = APIDG.invoice_line_number
AND POD.po_header_id = POH.po_header_id(+)
AND POD.po_line_id = POL.po_line_id(+)
AND POV.vendor_id = API.vendor_id
AND API.batch_id = APB.batch_id(+)
AND POL.line_type_id = POLT.line_type_id(+)
-- commented for bug 9669334
-- AND POL.item_id = MTLSI.inventory_item_id(+)
-- Bug 5483612. Added the NVL condition
-- AND NVL(MTLSI.organization_id, FSP.inventory_organization_id)
-- = FSP.inventory_organization_id
AND API.org_id = FSP.org_id
AND XDL.application_id = 200
AND XAH.application_id = 200 --bug5703586
-- bug5941716 starts
AND XAL.application_id = 200
AND XAH.accounting_entry_status_code='F'
AND APIDG.accounting_event_id = XAH.event_id
-- bug5941716 ends
AND XAH.ae_header_id = XAL.ae_header_id -- Bug 7284987 / 7392117
AND XDL.source_distribution_type = 'AP_INV_DIST' -- Bug 7284987 / 7392117
AND XDL.source_distribution_id_num_1 = APIDG.invoice_distribution_id
AND XAL.ae_header_id = XDL.ae_header_id
AND XAL.ae_line_num = XDL.ae_line_num
AND nvl(APIL.deferred_acctg_flag,'N') ='N' /*Bug 11727869*/ --bug 13845829
AND APIL.def_acctg_start_date is null /*Bug 11727869*/
AND APIL.def_acctg_end_date is null /*Bug 11727869*/
-- Bug 7284987 / 7392117
-- AND XDL.ae_header_id = XAH.ae_header_id
AND XAH.balance_type_code = 'A'
AND XAH.ledger_id = ALGT.ledger_id
AND (APIDG.org_id = ALGT.org_id OR
ALGT.org_id = -99)
AND XAL.accounting_class_code = AAGT.accounting_class_code;
l_debug_info := 'Insert Mass if Accounting Method Is Cash';
INSERT INTO ap_invoice_distributions_gt
(invoice_distribution_id,
invoice_id,
invoice_line_number,
po_distribution_id,
org_id,
accounting_event_id,
description,
asset_category_id,
quantity_invoiced,
historical_flag ,
corrected_quantity,
dist_code_combination_id,
line_type_lookup_code,
distribution_line_number,
accounting_date ,
corrected_invoice_dist_id,
related_id,
charge_applicable_to_dist_id,
asset_book_type_code,
set_of_books_id
)
SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
APID.invoice_distribution_id,
APID.invoice_id,
APID.invoice_line_number,
APID.po_distribution_id,
APID.org_id,
APID.accounting_event_id,
APID.description,
APID.asset_category_id,
APID.quantity_invoiced,
APID.historical_flag,
APID.corrected_quantity,
APID.dist_code_combination_id,
APID.line_type_lookup_code,
APID.distribution_line_number,
APID.accounting_date,
APID.corrected_invoice_dist_id,
APID.related_id,
APID.charge_applicable_to_dist_id,
APID.asset_book_type_code,
APID.set_of_books_id
FROM ap_invoice_distributions APID
WHERE APID.accounting_date <= P_acctg_date
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code IN ('ITEM','ACCRUAL')
AND apid.assets_tracking_flag = 'Y'
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P'
)
AND APID.posted_flag = 'Y'
AND APID.cash_posted_flag = 'Y'
AND APID.set_of_books_id = P_ledger_id
AND (APID.asset_book_type_code = P_bt_code OR
APID.asset_book_type_code IS NULL)
UNION ALL
SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
APID.invoice_distribution_id,
APID.invoice_id,
APID.invoice_line_number,
APID.po_distribution_id,
APID.org_id,
APID.accounting_event_id,
APID.description,
APID.asset_category_id,
APID.quantity_invoiced,
APID.historical_flag,
APID.corrected_quantity,
APID.dist_code_combination_id,
APID.line_type_lookup_code,
APID.distribution_line_number,
APID.accounting_date,
APID.corrected_invoice_dist_id,
APID.related_id,
APID.charge_applicable_to_dist_id,
nvl(APID.asset_book_type_code,item.asset_book_type_code),
APID.set_of_books_id
FROM ap_invoice_distributions APID,
ap_invoice_distributions_all item
WHERE APID.accounting_date <= P_acctg_date
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code NOT IN ('ITEM','ACCRUAL')
AND item.assets_tracking_flag = 'Y'
AND item.assets_addition_flag IN ('Y', 'U')
AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.related_id),
apid.corrected_invoice_dist_id) IS NOT NULL
AND nvl(nvl(apid.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id),
apid.related_id) =
item.invoice_distribution_id -- Bug 12660674
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P'
)
AND APID.posted_flag = 'Y'
AND APID.cash_posted_flag = 'Y'
AND APID.set_of_books_id = P_ledger_id
-- bug 8690407: add start
AND (APID.asset_book_type_code = P_bt_code
OR APID.asset_book_type_code IS NULL)
-- bug 8690407: add end
UNION ALL
-- Bug 13821160: Add code to allow Misc/Frieight/Tax lines (not allocated) interface to FA
SELECT /*+ index(apid AP_INVOICE_DISTRIBUTIONS_N31)*/
APID.invoice_distribution_id,
APID.invoice_id,
APID.invoice_line_number,
APID.po_distribution_id,
APID.org_id,
APID.accounting_event_id,
APID.description,
APID.asset_category_id,
APID.quantity_invoiced,
APID.historical_flag,
APID.corrected_quantity,
APID.dist_code_combination_id,
APID.line_type_lookup_code,
APID.distribution_line_number,
APID.accounting_date,
APID.corrected_invoice_dist_id,
APID.related_id,
APID.charge_applicable_to_dist_id,
APID.asset_book_type_code,
APID.set_of_books_id
FROM ap_invoice_distributions_all APID
WHERE APID.accounting_date <= P_acctg_date
AND APID.assets_addition_flag = 'U'
AND APID.line_type_lookup_code IN ('MISCELLANEOUS', 'FREIGHT', 'NONREC_TAX','REC_TAX')
AND APID.assets_tracking_flag = 'Y'
AND NVL(APID.charge_applicable_to_dist_id,APID.corrected_invoice_dist_id) IS NULL --Bug#14495604 added in case of invoice corrections
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P'
)
AND APID.posted_flag = 'Y'
AND APID.cash_posted_flag = 'Y'
AND APID.set_of_books_id = P_ledger_id
AND (APID.asset_book_type_code = P_bt_code
OR APID.asset_book_type_code IS NULL)
UNION ALL
SELECT satx.invoice_distribution_id,
satx.invoice_id,
satx.invoice_line_number,
satx.po_distribution_id,
satx.org_id,
satx.accounting_event_id,
satx.description,
satx.asset_category_id,
satx.quantity_invoiced,
'N',
satx.corrected_quantity,
satx.dist_code_combination_id,
satx.line_type_lookup_code,
satx.distribution_line_number,
satx.accounting_date,
satx.corrected_invoice_dist_id,
satx.related_id,
satx.charge_applicable_to_dist_id,
nvl(satx.asset_book_type_code, item.asset_book_type_code),
satx.set_of_books_id
FROM ap_invoice_distributions_all item,
ap_self_assessed_tax_dist satx
WHERE satx.accounting_date <= P_acctg_date
AND satx.assets_addition_flag = 'U'
AND item.assets_tracking_flag = 'Y'
AND item.assets_addition_flag IN ('Y', 'U')
AND satx.charge_applicable_to_dist_id IS NOT NULL
AND satx.charge_applicable_to_dist_id = item.invoice_distribution_id
AND ( satx.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = satx.project_id
) <> 'P' )
AND satx.posted_flag = 'Y'
AND satx.cash_posted_flag = 'Y'
AND satx.set_of_books_id = P_ledger_id
AND (satx.asset_book_type_code = P_bt_code OR
satx.asset_book_type_code IS NULL);
INSERT INTO FA_MASS_ADDITIONS_GT(
mass_addition_id,
asset_number,
tag_number,
description,
asset_category_id,
inventorial,
manufacturer_name,
serial_number,
model_number,
book_type_code,
date_placed_in_service,
transaction_type_code,
transaction_date,
fixed_assets_cost,
payables_units,
fixed_assets_units,
payables_code_combination_id,
expense_code_combination_id,
location_id,
assigned_to,
feeder_system_name,
create_batch_date,
create_batch_id,
last_update_date,
last_updated_by,
reviewer_comments,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
posting_status,
queue_name,
invoice_date,
invoice_created_by,
invoice_updated_by,
payables_cost,
invoice_id,
payables_batch_name,
depreciate_flag,
parent_mass_addition_id,
parent_asset_id,
split_merged_code,
ap_distribution_line_number,
post_batch_id,
add_to_asset_id,
amortize_flag,
new_master_flag,
asset_key_ccid,
asset_type,
deprn_reserve,
ytd_deprn,
beginning_nbv,
accounting_date,
created_by,
creation_date,
last_update_login,
salvage_value,
merge_invoice_number,
merge_vendor_number,
invoice_distribution_id,
invoice_line_number,
parent_invoice_dist_id,
ledger_id,
ledger_category_code,
warranty_number,
line_type_lookup_code,
po_distribution_id,
line_status
)
-- changed hint for bug 9669334
SELECT /*+ ordered use_hash(algt,aagt,polt,fsp) use_nl(pov,pod,pol,poh,xdl,xal,xah)
swap_join_inputs(algt) swap_join_inputs(fsp)
swap_join_inputs(polt) swap_join_inputs(aagt) */
DISTINCT -- 14240805 added distinct for case of multiple hist dists for one distribution
NULL,
NULL,
NULL,
--bugfix:5686771 added the NVL
RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)), -- Bug#6768121
-- changed the NVL into DECODE to replace the MTLSI table for bug 9669334
DECODE(APIDG.ASSET_CATEGORY_ID , NULL,
DECODE(POL.ITEM_ID,
NULL, NULL,
(SELECT MTLSI.ASSET_CATEGORY_ID
FROM MTL_SYSTEM_ITEMS MTLSI
WHERE POL.ITEM_ID = MTLSI.INVENTORY_ITEM_ID
AND MTLSI.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID )),
APIDG.ASSET_CATEGORY_ID),
NULL,
APIL.manufacturer,
APIL.serial_number,
APIL.model_number,
APIDG.asset_book_type_code,
NULL,
NULL,
trunc(API.invoice_date), -- Bug 14838337
-- 14240805 changed in case of multiple hist dists for one distribution/*fixed_assets_cost*/
SUM((NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)))
over (partition by APIDG.invoice_distribution_id),
decode(APIL.match_type, /* payables_units */
'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'QTY_CORRECTION', decode(APIDG.historical_flag,
'Y',
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'PRICE_CORRECTION', decode(APIDG.historical_flag,
'Y',
1,
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'ITEM_TO_SERVICE_PO', 1,
'ITEM_TO_SERVICE_RECEIPT', 1,
'AMOUNT_CORRECTION', 1,
decode(APIDG.quantity_invoiced,
Null,1,
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1))),
decode(APIL.match_type, /* fixed_assets_units */
'ITEM_TO_PO', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'ITEM_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'OTHER_TO_RECEIPT', decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
'QTY_CORRECTION', decode(APIDG.historical_flag,
'Y',
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1),
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'PRICE_CORRECTION', decode(APIDG.historical_flag,
'Y',
1,
decode(APIDG.corrected_quantity,
round(APIDG.corrected_quantity),
APIDG.corrected_quantity, 1)),
'ITEM_TO_SERVICE_PO', 1,
'ITEM_TO_SERVICE_RECEIPT', 1,
'AMOUNT_CORRECTION', 1,
decode(APIDG.quantity_invoiced,
Null,1,
decode(APIDG.quantity_invoiced,
round(APIDG.quantity_invoiced),
APIDG.quantity_invoiced, 1))),
decode(API.source, 'Intercompany', /* payables_code_combination_id */
Inv_Fa_Interface_Pvt.Get_Ic_Ccid(
APIDG.invoice_distribution_id,
APIDG.dist_code_combination_id,
APIDG.line_type_lookup_code),
decode(APIDG.po_distribution_id, NULL,
XAL.code_combination_id,
decode(POD.accrue_on_receipt_flag, 'Y',
POD.code_combination_id,
XAL.code_combination_id)
)
),
NULL,
NULL,
POD.deliver_to_person_id,
'ORACLE PAYABLES',
SYSDATE, -- Bug 5504510
P_request_id,
SYSDATE, -- Bug 5504510
P_user_id,
NULL,
rtrim(API.invoice_num),
rtrim(POV.segment1),
API.vendor_id,
rtrim(upper(POH.segment1)),
'NEW',
'NEW',
trunc(API.invoice_date), -- Bug 14838337
API.created_by,
API.last_updated_by,
SUM((NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)))
over (partition by APIDG.invoice_distribution_id),/*payabless_cost*/-- 14240805 changed in case of multiple hist dists for one distribution
API.invoice_id,
APB.batch_name,
NULL,
NULL,
NULL,
NULL,
APIDG.distribution_line_number,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
APIDG.accounting_date,
P_user_id,
SYSDATE, -- Bug 5504510
P_user_id,
NULL,
rtrim(API.invoice_num),
rtrim(POV.segment1),
APIDG.invoice_distribution_id,
APIL.line_number,
DECODE(APIDG.line_type_lookup_code,
'ITEM', decode(APIDG.corrected_invoice_dist_id, NULL,
APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id),
'ACCRUAL', decode(APIDG.corrected_invoice_dist_id, NULL,
APIDG.invoice_distribution_id, APIDG.corrected_invoice_dist_id), -- bug 9001504
'IPV', decode(APIDG.corrected_invoice_dist_id, NULL,
APIDG.related_id, APIDG.corrected_invoice_dist_id), -- bug 9001504, 12660674
'ERV', APIDG.related_id,
APIDG.charge_applicable_to_dist_id
),
ALGT.ledger_id,
ALGT.ledger_category_code,
APIL.warranty_number,
APIDG.line_type_lookup_code,
POD.po_distribution_id,
'NEW'
FROM ap_invoice_distributions_gt APIDG,
ap_invoice_lines_all APIL,
ap_invoices_all API,
AP_PAYMENT_HIST_DISTS APHD, --Bug9967535
financials_system_params_all FSP, -- changed table order # 9669334
ap_batches_all APB,
po_distributions_all POD,
po_headers_all POH,
po_lines_all POL,
po_vendors POV,
po_line_types_b POLT,
-- mtl_system_items MTLSI,
xla_distribution_links XDL,
xla_ae_lines XAL,
ap_acct_class_code_gt AAGT ,
xla_ae_headers XAH,
ap_alc_ledger_gt ALGT
WHERE APIDG.po_distribution_id = POD.po_distribution_id(+)
AND API.invoice_id = APIL.invoice_id
AND APIL.invoice_id = APIDG.invoice_id
AND APIL.line_number = APIDG.invoice_line_number
AND APIDG.invoice_distribution_id = APHD.invoice_distribution_id --Bug9967535
AND POD.po_header_id = POH.po_header_id(+)
AND POD.po_line_id = POL.po_line_id(+)
AND POV.vendor_id = API.vendor_id
AND API.batch_id = APB.batch_id(+)
AND POL.line_type_id = POLT.line_type_id(+)
-- commented for bug 9669334
-- AND POL.item_id = MTLSI.inventory_item_id(+)
-- Bug 5483612. Added the NVL condition
-- AND NVL(MTLSI.organization_id, FSP.inventory_organization_id)
-- = FSP.inventory_organization_id
AND API.org_id = FSP.org_id
AND XDL.application_id = 200
AND XAH.application_id = 200 --bug5703586
-- bug5941716 starts
AND XAL.application_id = 200
AND XAH.accounting_entry_status_code='F'
--Bug9967535 Removing old code, adding new. Earlier code was comparing invoice dists event id with xla headers event id,
--which do not exist in XLA in cash basis.New code is having joins with payments data
AND APHD.accounting_event_id = XAH.event_id --Bug9967535
-- bug5941716 ends
AND XAH.ae_header_id = XAL.ae_header_id -- Bug 7284987 / 7392117
--Bug9967535 starts, Removing old code, adding new to point to payment parameters
AND XDL.source_distribution_type = 'AP_PMT_DIST'
AND XDL.applied_to_distribution_type ='AP_INV_DIST'
AND XDL.source_distribution_id_num_1 = APHD.payment_hist_dist_id
AND XDL.APPLIED_TO_DIST_ID_NUM_1 = APIDG.invoice_distribution_id
AND AP_INVOICES_UTILITY_PKG.get_payment_status( API.invoice_id)= 'Y'
AND Get_cash_gl_transfer(API.invoice_id)= 'Y'
-- Bug9967535 ends
AND XAL.ae_header_id = XDL.ae_header_id
AND XAL.ae_line_num = XDL.ae_line_num
AND nvl(APIL.deferred_acctg_flag,'N') ='N' /*Bug 11727869*/ --bug 13845829
AND APIL.def_acctg_start_date is null /*Bug 11727869*/
AND APIL.def_acctg_end_date is null /*Bug 11727869*/
AND XAH.balance_type_code = 'A'
AND XAH.ledger_id = ALGT.ledger_id
AND (APIDG.org_id = ALGT.org_id OR
ALGT.org_id = -99)
AND XAL.accounting_class_code = AAGT.accounting_class_code;
/* BUG # 7648502. Added the update statement to
update the assets addition flag to N which are
not picked up by fass addition gt table but picked by
distributions gt table. by stamping these to N will
avoid from picking up again while loading distributions gt
*/
/* Modified the query for performance bug 8729684: start */
UPDATE ap_invoice_distributions_all AID
SET AID.assets_addition_flag = 'N'
WHERE AID.invoice_distribution_id IN
(SELECT APIDG.invoice_distribution_id
FROM ap_invoice_distributions_gt APIDG
where charge_applicable_to_dist_id is null
AND NOT EXISTS
(SELECT 1
FROM fa_mass_additions_gt FAGT
WHERE APIDG.INVOICE_DISTRIBUTION_ID = FAGT.INVOICE_DISTRIBUTION_ID
)
);
UPDATE /*+ index(AID AP_INVOICE_DISTRIBUTIONS_U2) */
AP_INVOICE_DISTRIBUTIONS_ALL AID
SET AID.ASSETS_ADDITION_FLAG = 'N'
WHERE
AID.INVOICE_DISTRIBUTION_ID IN
( SELECT APIDG.INVOICE_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_GT APIDG
WHERE
CHARGE_APPLICABLE_TO_DIST_ID IS NOT NULL
AND NOT EXISTS
(
SELECT 1 FROM FA_MASS_ADDITIONS_GT FAGT
where APIDG.INVOICE_DISTRIBUTION_ID=FAGT.INVOICE_DISTRIBUTION_ID
)
)
AND EXISTS
(
SELECT /*+ index(AP_INVOICE_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIONS_U2)*/ 1 FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_DISTRIBUTION_ID = AID.CHARGE_APPLICABLE_TO_DIST_ID
AND ASSETS_ADDITION_FLAG = 'N'
);
UPDATE AP_SELF_ASSESSED_TAX_DIST_ALL AID
SET AID.assets_addition_flag = 'N'
WHERE AID.invoice_distribution_id IN
(SELECT APIDG.invoice_distribution_id
FROM ap_invoice_distributions_gt APIDG)
AND AID.invoice_distribution_id NOT IN
(SELECT FAGT.invoice_distribution_id
FROM fa_mass_additions_gt FAGT)
-- bug 7215835: add end
-- bug 8690407: add start
and exists (select 1 from ap_invoice_distributions_all
where invoice_distribution_id = aid.charge_applicable_to_dist_id
and nvl(assets_addition_flag, 'N') = 'N');
END Insert_Mass;
PROCEDURE Insert_Discount(
P_acctg_date IN DATE,
P_ledger_id IN NUMBER,
P_user_id IN NUMBER,
P_request_id IN NUMBER,
P_bt_code IN VARCHAR2,
P_count OUT NOCOPY NUMBER,
P_calling_sequence IN VARCHAR2 DEFAULT NULL) IS
--
l_current_calling_sequence VARCHAR2(2000);
l_api_name CONSTANT VARCHAR2(100) := 'INSERT_DISCOUNT';
SELECT invoice_payment_id
FROM ap_invoice_payments APIP
WHERE APIP.assets_addition_flag = 'U'
AND APIP.posted_flag = 'Y'
AND APIP.accounting_date <= P_acctg_date
AND APIP.set_of_books_id = P_ledger_id
AND APIP.invoice_payment_id IN (
SELECT + INDEX(aphd ap_payment_hist_dists_n5) -- Bug 8305129
APHD.invoice_payment_id
FROM ap_payment_hist_dists APHD,
ap_invoice_distributions_all APID
WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
AND APIP.ACCOUNTING_EVENT_ID=APHD.ACCOUNTING_EVENT_ID --bug5461146
AND APHD.invoice_distribution_id = APID.invoice_distribution_id
AND APHD.pay_dist_lookup_code = 'DISCOUNT'
AND NVL(APID.assets_addition_flag,'N') <> 'N' -- bug 9001504
AND (APID.asset_book_type_code = P_bt_code -- Bug 5581999
OR APID.asset_book_type_code IS NULL)
bug 4475705
AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND APID.assets_tracking_flag = 'Y')
OR EXISTS -- Bug 8305129 : Replaced 2 EXISTS clause with 1
( SELECT 'X'
FROM ap_invoice_distributions_all APIDV
WHERE NVL(APID.related_id,APID.charge_applicable_to_dist_id) =
APIDV.invoice_distribution_id
AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
AND APIDV.assets_tracking_flag = 'Y'
)
)
);*/
'Insert_Discount';
l_debug_info := 'Insert into FA_MASS_ADDITIONS_GT';
INSERT INTO FA_MASS_ADDITIONS_GT(
mass_addition_id,
asset_number,
tag_number,
description,
asset_category_id,
inventorial,
manufacturer_name,
serial_number,
model_number,
book_type_code,
date_placed_in_service,
transaction_type_code,
transaction_date,
fixed_assets_cost,
payables_units,
fixed_assets_units,
payables_code_combination_id,
expense_code_combination_id,
location_id,
assigned_to,
feeder_system_name,
create_batch_date,
create_batch_id,
last_update_date,
last_updated_by,
reviewer_comments,
invoice_number,
vendor_number,
po_vendor_id,
po_number,
posting_status,
queue_name,
invoice_date,
invoice_created_by,
invoice_updated_by,
payables_cost,
invoice_id,
payables_batch_name,
depreciate_flag,
parent_mass_addition_id,
parent_asset_id,
split_merged_code,
ap_distribution_line_number,
post_batch_id,
add_to_asset_id,
amortize_flag,
new_master_flag,
asset_key_ccid,
asset_type,
deprn_reserve,
ytd_deprn,
beginning_nbv,
accounting_date,
created_by,
creation_date,
last_update_login,
salvage_value,
merge_invoice_number,
merge_vendor_number,
invoice_distribution_id,
invoice_line_number,
parent_invoice_dist_id,
ledger_id,
ledger_category_code,
warranty_number,
line_type_lookup_code,
po_distribution_id,
line_status,
invoice_payment_id --bug5485118
) --8393259 xdl is removed from leading hint
/*Bug12703009: Modified the hints below*/ /* bug#14712606 - modified the hint */
SELECT /*+ leading( apip aphd apid ) use_nl(APHD APID POD XAH poh) use_hash( algt ) use_hash( aagt ) swap_join_inputs( algt ) swap_join_inputs( aagt ) */ NULL, --bug5941716
NULL,
NULL,
APL.displayed_field, -- bug 8927096: modify
NULL,
'YES',
NULL,
NULL,
NULL,
DECODE(APID.asset_book_type_code, P_bt_code,
P_bt_code, APID.asset_book_type_code),
NULL,
NULL,
trunc(API.invoice_date), -- Bug 14838337
(NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*fixed_assets_cost*/
decode(APIL.match_type, /* payables_units */
'ITEM_TO_PO', decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
'ITEM_TO_RECEIPT', decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
'OTHER_TO_RECEIPT', decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
'QTY_CORRECTION', decode(APID.historical_flag,
'Y',
decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
decode(APID.corrected_quantity,
round(APID.corrected_quantity),
APID.corrected_quantity, 1)),
'PRICE_CORRECTION', decode(APID.historical_flag,
'Y',
1,
decode(APID.corrected_quantity,
round(APID.corrected_quantity),
APID.corrected_quantity, 1)),
'ITEM_TO_SERVICE_PO', 1,
'ITEM_TO_SERVICE_RECEIPT', 1,
'AMOUNT_CORRECTION', 1,
decode(APID.quantity_invoiced,
Null,1,
decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1))),
decode(APIL.match_type, /* fixed_assets_units */
'ITEM_TO_PO', decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
'ITEM_TO_RECEIPT', decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
'OTHER_TO_RECEIPT', decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
'QTY_CORRECTION', decode(APID.historical_flag,
'Y',
decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1),
decode(APID.corrected_quantity,
round(APID.corrected_quantity),
APID.corrected_quantity, 1)),
'PRICE_CORRECTION', decode(APID.historical_flag,
'Y',
1,
decode(APID.corrected_quantity,
round(APID.corrected_quantity),
APID.corrected_quantity, 1)),
'ITEM_TO_SERVICE_PO', 1,
'ITEM_TO_SERVICE_RECEIPT', 1,
'AMOUNT_CORRECTION', 1,
decode(APID.quantity_invoiced,
Null,1,
decode(APID.quantity_invoiced,
round(APID.quantity_invoiced),
APID.quantity_invoiced, 1))),
decode(APID.po_distribution_id, NULL, /* payables_code_combination_id */
XAL.code_combination_id,
decode(POD.accrue_on_receipt_flag, 'Y',
POD.code_combination_id,
XAL.code_combination_id)
),
NULL,
NULL,
POD.deliver_to_person_id,
'ORACLE PAYABLES',
SYSDATE, -- Bug 5504510
P_request_id,
SYSDATE, -- Bug 5504510
P_user_id,
NULL,
rtrim(API.invoice_num),
rtrim(POV.segment1),
API.vendor_id,
rtrim(upper(POH.segment1)),
'NEW',
'NEW',
trunc(API.invoice_date), -- Bug 14838337
API.created_by,
API.last_updated_by,
(NVL(XDL.unrounded_accounted_dr,0) - NVL(XDL.unrounded_accounted_cr,0)),/*payabless_cost*/
API.invoice_id,
APB.batch_name,
NULL,
NULL,
NULL,
NULL,
APID.distribution_line_number,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
APID.accounting_date,
P_user_id,
SYSDATE, -- Bug 5504510
P_user_id,
NULL,
rtrim(API.invoice_num),
rtrim(POV.segment1),
APID.invoice_distribution_id, -- Bug 5648304.
APIL.line_number,
DECODE(APID.line_type_lookup_code,
'ITEM', decode(APID.corrected_invoice_dist_id, NULL,
APID.invoice_distribution_id, APID.corrected_invoice_dist_id),
'ACCRUAL', decode(APID.corrected_invoice_dist_id, NULL,
APID.invoice_distribution_id, APID.corrected_invoice_dist_id),
NVL(APID.charge_applicable_to_dist_id, APID.invoice_distribution_id)), -- Bug 13821160
ALGT.ledger_id,
ALGT.ledger_category_code,
APIL.warranty_number,
'DISCOUNT',
POD.po_distribution_id,
'NEW',
APIP.invoice_payment_id
FROM ap_invoice_distributions_all APID,
ap_invoice_lines_all APIL,
ap_invoice_payments_all APIP,
ap_payment_hist_dists APHD,
ap_invoices_all API,
ap_batches_all APB,
po_distributions_all POD,
po_headers_all POH,
--po_lines_all POL, /*Bug12703009: Commented the unused table*/
po_vendors POV,
--po_line_types_b POLT,
xla_distribution_links XDL,
xla_ae_headers XAH,
xla_ae_lines XAL,
ap_alc_ledger_gt ALGT,
ap_acct_class_code_gt AAGT,
ap_lookup_codes APL -- bug 8927096: add
WHERE /*APIP.invoice_payment_id = l_invoice_pay_id --Bug 12703009 commented the extra filter as we no more use the cursor
AND */ APIP.accounting_event_id = APHD.accounting_event_id --Bug 12703009 added extra join condition as per bug 5461146
AND APIP.invoice_payment_id = APHD.invoice_payment_id
AND APHD.invoice_distribution_id = APID.invoice_distribution_id
AND APHD.pay_dist_lookup_code = 'DISCOUNT'
AND APIP.assets_addition_flag = 'U'
AND APIP.posted_flag = 'Y'
AND APIP.accounting_date <= P_acctg_date
AND APIP.set_of_books_id = P_ledger_id
AND APID.assets_addition_flag <> 'N' -- bug 9001504
AND (APID.asset_book_type_code = P_bt_code --Bug 12703009 Added back the conditions imposed in Bug 5581999 in the cursor C_Discount
OR APID.asset_book_type_code IS NULL)
-- bug 8927096: add start
AND APL.lookup_code='DISCOUNT'
AND APL.lookup_type='AE LINE TYPE'
-- bug 8927096: add end
/* bug 4475705 */
AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND APID.assets_tracking_flag = 'Y')
/*OR EXISTS --Bug 12703009: commented below two exists
( SELECT 'X'
FROM ap_invoice_distributions_all APIDV
WHERE APID.related_id =
APIDV.invoice_distribution_id
AND APID.invoice_distribution_id <> APID.related_id --bug6415366
AND APIDV.assets_tracking_flag = 'Y')
OR EXISTS
( SELECT 'X'
FROM ap_invoice_distributions_all APIDC
WHERE APID.charge_applicable_to_dist_id =
APIDC.invoice_distribution_id
AND APIDC.assets_tracking_flag = 'Y')*/
OR EXISTS --Bug 12703009 : clubbed two exists into one as per Bug 8305129 and also added condition for corrected invoices as per bug 9001504
( SELECT /*+ push_subq no_unnest */ 'X'
FROM ap_invoice_distributions_all APIDV
WHERE COALESCE(APID.charge_applicable_to_dist_id, apid.corrected_invoice_dist_id, APID.related_id) =
APIDV.invoice_distribution_id -- Bug 12660674. Changed order of columns.
AND APIDV.invoice_distribution_id <> NVL(APIDV.related_id, -1)
AND APIDV.assets_tracking_flag = 'Y'
)
-- Bug 13821160: Added code to allow Misc/Freight/Tax lines (not allocated) to interface to FA
OR ( APID.line_type_lookup_code IN ('MISCELLANEOUS','FREIGHT','NONREC_TAX','REC_TAX')
AND APID.assets_tracking_flag = 'Y'
AND charge_applicable_to_dist_id IS NULL)
)
AND APID.po_distribution_id = POD.po_distribution_id(+)
AND API.invoice_id = APIL.invoice_id
AND APIL.invoice_id = APID.invoice_id
AND APIL.line_number = APID.invoice_line_number
AND POD.po_header_id = POH.po_header_id(+)
--AND POD.po_line_id = POL.po_line_id(+) /*Bug12703009: Commented the unused table join*/
AND POV.vendor_id = API.vendor_id
AND API.batch_id = APB.batch_id(+)
-- AND POL.line_type_id = POLT.line_type_id(+)
AND (XDL.source_distribution_id_num_1 = APHD.payment_hist_dist_id
OR /*Bug 13703091 begin*/
(XAH.event_type_code = 'PAYMENT CANCELLED'
AND XDL.source_distribution_id_num_1 = APHD.reversed_pay_hist_dist_id))/*Bug 13703091 end*/
AND XAL.ae_header_id = XDL.ae_header_id
AND XAL.ae_line_num = XDL.ae_line_num
/*bug12432394 Start*/
--AND XDL.ae_header_id = XAH.ae_header_id
AND XAH.ae_header_id = XAL.ae_header_id
AND XDL.source_distribution_type = 'AP_PMT_DIST'
/*bug12432394 End */ /*Bug 13703091: commenting was not proper*/
AND nvl(APIL.deferred_acctg_flag,'N') ='N' /*Bug 11727869*/ --bug 13845829
AND APIL.def_acctg_start_date is null /*Bug 11727869*/
AND APIL.def_acctg_end_date is null /*Bug 11727869*/
AND XAH.balance_type_code = 'A'
AND XAH.ledger_id = ALGT.ledger_id
AND XDL.application_id = 200 --bug5703586
AND XAH.application_id = 200 --bug5703586
--bug5941716 starts
AND XAL.application_id = 200
AND XAH.accounting_entry_status_code='F'
AND APIP.accounting_event_id = XAH.event_id /*for bug#6932371 attached discounts to APIP table
instead of APID table*/
--bug5941716 ends
AND (APID.org_id = ALGT.org_id OR
ALGT.org_id = -99)
AND XAL.accounting_class_code = AAGT.accounting_class_code
-- Bug 13821160: Added code to skip capital project dist
AND ( APID.project_id IS NULL
OR
(SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U')
FROM pa_project_types_all ptype,
pa_projects_all proj
WHERE proj.project_type = ptype.project_type
AND ptype.org_id = proj.org_id
AND proj.project_id = APID.project_id
) <> 'P' );
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Inserted: '
||TO_CHAR(l_count));
END Insert_Discount;
UPDATE /*+ INDEX(apid ap_invoice_distributions_n6) */ -- Bug 8305129
ap_invoice_distributions_all APID
SET APID.assets_addition_flag = 'N',
APID.program_update_date = SYSDATE,
APID.program_application_id = FND_GLOBAL.prog_appl_id,
APID.program_id = FND_GLOBAL.conc_program_id,
APID.request_id = l_request_id
WHERE APID.assets_addition_flag = 'U'
AND APID.org_id IN (SELECT org_id
FROM ap_system_parameters)
AND APID.set_of_books_id = l_ledger_id
AND APID.posted_flag = 'Y'
AND APID.assets_tracking_flag = 'Y'
AND EXISTS -- Added EXISTS for bug 9669334
(SELECT 'X'
FROM mtl_system_items MTLSI,
po_distributions_all POD,
po_line_locations_all PLL,
po_lines_all POL
WHERE POD.po_distribution_id = APID.po_distribution_id
AND PLL.line_location_id = POD.line_location_id
AND POL.po_line_id = PLL.po_line_id
AND POL.item_id = MTLSI.inventory_item_id
AND MTLSI.organization_id = POD.destination_organization_id
AND MTLSI.comms_nl_trackable_flag = 'Y'
AND MTLSI.asset_creation_code = 1);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Updated: ' ||TO_CHAR(l_count));
UPDATE ap_self_assessed_tax_dist_all APID
SET APID.assets_addition_flag = 'N',
APID.program_update_date = SYSDATE,
APID.program_application_id = FND_GLOBAL.prog_appl_id,
APID.program_id = FND_GLOBAL.conc_program_id,
APID.request_id = l_request_id
WHERE APID.assets_addition_flag = 'U'
AND APID.org_id IN (SELECT org_id
FROM ap_system_parameters)
AND APID.set_of_books_id = l_ledger_id
AND APID.posted_flag = 'Y'
AND APID.assets_tracking_flag = 'Y'
AND EXISTS -- Added EXISTS for bug 9669334
(SELECT 'X'
FROM mtl_system_items MTLSI,
po_distributions_all POD,
po_line_locations_all PLL,
po_lines_all POL
WHERE POD.po_distribution_id = APID.po_distribution_id
AND PLL.line_location_id = POD.line_location_id
AND POL.po_line_id = PLL.po_line_id
AND POL.item_id = MTLSI.inventory_item_id
AND MTLSI.organization_id = POD.destination_organization_id
AND MTLSI.comms_nl_trackable_flag = 'Y'
AND MTLSI.asset_creation_code = 1);
l_debug_info := ' Calling Insert_Mass';
Insert_Mass(
l_acctg_date,
l_ledger_id,
l_user_id,
l_request_id,
P_bt_code,
l_count,
l_primary_accounting_method,
l_current_calling_sequence);
'Total Non-Discount Records Inserted into FA Temp Table: '
||TO_CHAR(l_total));
l_debug_info := 'Calling Project API for Inserting PA Adjustments';
PA_MASS_ADDITIONS_CREATE_PKG.Insert_Mass(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_pa_return_status,
x_msg_count => l_pa_msg_count,
x_msg_data => l_pa_msg_data,
x_count => l_count1,
p_acctg_date => l_acctg_date,
p_ledger_id => l_ledger_id,
p_user_id => l_user_id,
p_request_id => l_request_id,
p_bt_code => P_bt_code,
p_primary_accounting_method => l_primary_accounting_method,
p_calling_sequence => 'Oracle Payables Mass Addition Process');
'Total Non-Discount Records Inserted into FA Temp Table '
||'including PA Adjustment Lines: '
||TO_CHAR(l_total1));
l_debug_info := ' Calling Insert_Discount';
Insert_Discount(
l_acctg_date,
l_ledger_id,
l_user_id,
l_request_id,
P_bt_code,
l_count,
l_current_calling_sequence);
'Total Discount Records Inserted into FA Temp Table: '
||TO_CHAR(l_total));
l_debug_info := 'Calling Project API for Inserting PA Discount Adjustments';
PA_MASS_ADDITIONS_CREATE_PKG.Insert_Discounts(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_pa_return_status,
x_msg_count => l_pa_msg_count,
x_msg_data => l_pa_msg_data,
x_count => l_count1,
p_acctg_date => l_acctg_date,
p_ledger_id => l_ledger_id,
p_user_id => l_user_id,
p_request_id => l_request_id,
p_bt_code => P_bt_code,
p_primary_accounting_method => l_primary_accounting_method,
p_calling_sequence => 'Oracle Payables Mass Addition Process');
'Total Discount Records Inserted into FA Temp Table '
||'including PA Adjustment Lines: '
||TO_CHAR(l_total));
'Grand Total of Records Inserted into FA Temp Table: '
||TO_CHAR(l_total1));
l_debug_info := 'Calling FA API for inserting Discount Assets ';
l_debug_info := 'Update Invoice Distributions which are transferred to Asset ';
For I in (Select count(*) "CNT", invoice_distribution_id
from fa_mass_additions_gt
where line_status = 'PROCESSED'
and line_type_lookup_code <> 'DISCOUNT'
and ledger_id = l_ledger_id
group by invoice_distribution_id
having count(*) > 1) loop
/*Bug13703091: Added exception and self assessed tax SQL below*/
BEGIN
Select invoice_id
into L_debug_inv_id
from
(Select distinct aid.invoice_id "INVOICE_ID"
from ap_invoice_distributions_all aid
Where aid.invoice_distribution_id = i.invoice_distribution_id
UNION
Select distinct astx.invoice_id "INVOICE_ID"
from ap_self_assessed_tax_dist_all astx
Where astx.invoice_distribution_id = i.invoice_distribution_id);
WHEN MATCHED THEN UPDATE SET apid.assets_addition_flag = 'Y',
apid.program_update_date = sysdate,
apid.program_application_id = fnd_global.prog_appl_id,
apid.program_id = fnd_global.conc_program_id,
apid.request_id = fnd_global.conc_request_id,
apid.asset_book_type_code = fmag.book_type_code
Where apid.assets_addition_flag <> 'Y' /*10368924*/;
WHEN MATCHED THEN UPDATE SET apid.assets_addition_flag = 'Y',
apid.program_update_date = sysdate,
apid.program_application_id = fnd_global.prog_appl_id,
apid.program_id = fnd_global.conc_program_id,
apid.request_id = fnd_global.conc_request_id,
apid.asset_book_type_code = fmag.book_type_code
Where apid.assets_addition_flag <> 'Y' /*10368924*/;
l_debug_info := 'Update Invoice Distributions which are not transferred to Asset ';
UPDATE ap_invoice_distributions_all APID
SET APID.assets_addition_flag = 'N',
APID.program_update_date = SYSDATE,
APID.program_application_id = FND_GLOBAL.prog_appl_id,
APID.program_id = FND_GLOBAL.conc_program_id,
APID.request_id = FND_GLOBAL.conc_request_id,
APID.asset_book_type_code = P_bt_code
WHERE APID.invoice_distribution_id IN
(SELECT FMAG.invoice_distribution_id
FROM fa_mass_additions_gt FMAG
WHERE FMAG.line_status = 'REJECTED'
AND FMAG.ledger_id = l_ledger_id
AND fmag.line_type_lookup_code <> 'DISCOUNT')
AND APID.assets_addition_flag = 'U';
UPDATE ap_self_assessed_tax_dist_all APID
SET APID.assets_addition_flag = 'N',
APID.program_update_date = SYSDATE,
APID.program_application_id = FND_GLOBAL.prog_appl_id,
APID.program_id = FND_GLOBAL.conc_program_id,
APID.request_id = FND_GLOBAL.conc_request_id,
APID.asset_book_type_code = P_bt_code
WHERE APID.invoice_distribution_id IN
(SELECT FMAG.invoice_distribution_id
FROM fa_mass_additions_gt FMAG
WHERE FMAG.line_status = 'REJECTED'
AND FMAG.ledger_id = l_ledger_id
AND fmag.line_type_lookup_code <> 'DISCOUNT')
AND APID.assets_addition_flag = 'U';
l_debug_info := 'Update Invoice Payments which are transferred to Asset ';
UPDATE ap_invoice_payments_all APIP
SET APIP.assets_addition_flag = 'Y'
WHERE APIP.assets_addition_flag = 'U'
AND APIP.posted_flag = 'Y'
AND APIP.set_of_books_id = l_ledger_id
AND APIP.invoice_payment_id IN (
SELECT APHD.invoice_payment_id
FROM ap_payment_hist_dists APHD,
ap_invoice_distributions_all APID,
fa_mass_additions_gt FMAG
WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
AND APHD.invoice_distribution_id =
APID.invoice_distribution_id
AND APID.invoice_distribution_id =
FMAG.parent_invoice_dist_id
AND FMAG.line_type_lookup_code = 'DISCOUNT'
AND FMAG.line_status = 'PROCESSED'
AND FMAG.ledger_id = l_ledger_id);
'No of Invoice Payment Record Updated '
||'after successfully transferred to Asset: '
||TO_CHAR(l_count));
UPDATE ap_invoice_payments_all APIP
SET APIP.assets_addition_flag = 'N'
WHERE APIP.assets_addition_flag = 'U'
AND APIP.posted_flag = 'Y'
AND APIP.set_of_books_id = l_ledger_id
AND APIP.invoice_payment_id IN (
SELECT APHD.invoice_payment_id
FROM ap_payment_hist_dists APHD,
ap_invoice_distributions_all APID,
fa_mass_additions_gt FMAG
WHERE APIP.invoice_payment_id = APHD.invoice_payment_id
AND APHD.invoice_distribution_id =
APID.invoice_distribution_id
AND APID.invoice_distribution_id =
FMAG.parent_invoice_dist_id
AND FMAG.line_status = 'REJECTED'
AND FMAG.line_type_lookup_code = 'DISCOUNT'
AND FMAG.ledger_id = l_ledger_id);
'No of Invoice Payment Record Updated '
||'after failed to transfer to Asset: '
||TO_CHAR(l_count));
l_debug_info := 'Update PA Adjustments which are processed
or rejected by FA API ';
PA_MASS_ADDITIONS_CREATE_PKG.Update_Mass(
p_api_version => 1.0,
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_pa_return_status,
x_msg_count => l_pa_msg_count,
x_msg_data => l_pa_msg_data,
p_request_id => l_request_id);
END IF; -- Discount record inserted