[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;
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';
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)*/ --8236268
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 decode(apid.line_type_lookup_code,'ITEM', apid.assets_tracking_flag,
'ACCRUAL',apid.assets_tracking_flag, nvl(item.assets_tracking_flag, 'N') ) = 'Y'
AND decode(apid.line_type_lookup_code,'ITEM',1,'ACCRUAL',1,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) IS NOT NULL
AND decode(apid.line_type_lookup_code,'ITEM', null,'ACCRUAL',null,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) =
item.invoice_distribution_id(+)
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
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 ;
/* SELECT 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' )
OR 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')
)
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
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 ; */
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
)
SELECT /*+ leading(APIDG aagt algt) use_hash(algt) use_hash(aagt) index(XDL XLA_DISTRIBUTION_LINKS_N3) USE_NL(API) index( API AP_INVOICES_U1) */ NULL,-- changed hint Bug 7284987 / 7392117 /7438251
NULL,
NULL,
--bugfix:5686771 added the NVL
RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)),
-- Bug#6768121
nvl(APIDG.asset_category_id , MTLSI.asset_category_id),
NULL,
APIL.manufacturer,
APIL.serial_number,
APIL.model_number,
APIDG.asset_book_type_code,
NULL,
NULL,
API.invoice_date,
(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',
API.invoice_date,
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),
'IPV', APIDG.related_id,
'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, --Bug 7284987 / 7392117
ap_invoice_lines_all APIL,
ap_invoices_all API,
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,
financials_system_params_all FSP,
xla_distribution_links XDL,
xla_ae_headers XAH,
xla_ae_lines XAL,
ap_alc_ledger_gt ALGT,
ap_acct_class_code_gt AAGT
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(+)
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
-- 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
AND (APIDG.asset_book_type_code = P_bt_code -- Bug 5581999
OR APIDG.asset_book_type_code IS NULL); -- Bug 6980939
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)*/ --8236268
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 decode(apid.line_type_lookup_code,'ITEM', apid.assets_tracking_flag,
'ACCRUAL',apid.assets_tracking_flag, nvl(item.assets_tracking_flag, 'N') ) = 'Y'
AND decode(apid.line_type_lookup_code,'ITEM',1,'ACCRUAL',1,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) IS NOT NULL
AND decode(apid.line_type_lookup_code,'ITEM', null,'ACCRUAL',null,nvl(apid.charge_applicable_to_dist_id, apid.related_id)) =
item.invoice_distribution_id(+)
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
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;
/* SELECT 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' )
OR 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')
)
AND ( APID.project_id IS NULL
OR ( SELECT decode(ptype.project_type_class_code,'CAPITAL','P','U') --Call Expanded for Bug 7284987 / 7392117
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 ; */
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
)
SELECT /*+ leading(APIDG aagt algt) use_hash(algt) use_hash(aagt) index(XDL XLA_DISTRIBUTION_LINKS_N3) USE_NL(API) index( API AP_INVOICES_U1) */ NULL,-- changed hint Bug 7284987 / 7392117 /7438251
NULL,
NULL,
--bugfix:5686771 added the NVL
RTRIM(SUBSTRB(NVL(APIDG.description,APIL.description),1,80)),
-- Bug#6768121
nvl(APIDG.asset_category_id , MTLSI.asset_category_id),
NULL,
APIL.manufacturer,
APIL.serial_number,
APIL.model_number,
APIDG.asset_book_type_code,
NULL,
NULL,
API.invoice_date,
(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',
API.invoice_date,
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),
'IPV', APIDG.related_id,
'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, -- Bug 7284987 / 7392117
ap_invoice_lines_all APIL,
ap_invoices_all API,
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,
financials_system_params_all FSP,
xla_distribution_links XDL,
xla_ae_headers XAH,
xla_ae_lines XAL,
ap_alc_ledger_gt ALGT,
ap_acct_class_code_gt AAGT
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(+)
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 APIDG.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
-- 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
AND (APIDG.asset_book_type_code = P_bt_code -- Bug 5581999
OR APIDG.asset_book_type_code IS NULL);
/* 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
*/
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)
AND AID.invoice_distribution_id NOT IN
(SELECT FAGT.invoice_distribution_id
FROM fa_mass_additions_gt FAGT);
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 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 APID.assets_addition_flag = 'Y'
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
( SELECT 'X'
FROM ap_invoice_distributions_all APIDV
WHERE APID.related_id =
APIDV.invoice_distribution_id
AND APIDV.invoice_distribution_id <> APIDV.related_id
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')
)
);
'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
)
SELECT /*+ leading ( apip aphd xdl) use_hash ( algt ) use_hash ( aagt ) swap_join_inputs ( algt ) swap_join_inputs ( aagt ) */ NULL, --bug5941716
NULL,
NULL,
--bugfix:5686771 added the NVL condition
RTRIM(SUBSTRB(NVL(APID.description,APIL.description),1,80)),
NULL,
'YES',
NULL,
NULL,
NULL,
DECODE(APID.asset_book_type_code, P_bt_code,
P_bt_code, APID.asset_book_type_code),
NULL,
NULL,
API.invoice_date,
(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',
API.invoice_date,
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),
APID.charge_applicable_to_dist_id),
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,
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
WHERE APIP.invoice_payment_id = l_invoice_pay_id
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 = 'Y'
/* bug 4475705 */
AND ( (APID.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
AND APID.assets_tracking_flag = 'Y')
OR 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')
)
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(+)
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
AND XAL.ae_header_id = XDL.ae_header_id
AND XAL.ae_line_num = XDL.ae_line_num
AND XDL.ae_header_id = XAH.ae_header_id
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;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Inserted: '
||TO_CHAR(l_count));
END Insert_Discount;
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 = 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 ('Y', '1') =
(SELECT MTLSI.comms_nl_trackable_flag,
MTLSI.asset_creation_code
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);
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'No of Records Updated: ' ||TO_CHAR(l_count));
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 FA API for inserting Non-Discount Assets ';
l_debug_info := 'Update Invoice Distributions which are transferred to Asset ';
UPDATE ap_invoice_distributions_all APID
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 = P_bt_code
WHERE APID.invoice_distribution_id IN
(SELECT FMAG.invoice_distribution_id
FROM fa_mass_additions_gt FMAG
WHERE FMAG.line_status = 'PROCESSED'
AND FMAG.ledger_id = l_ledger_id)
AND APID.assets_addition_flag = 'U'; */
--New Update Stmt addedd
UPDATE /*+ bypass_ujvc */ --8236268
(SELECT apid.assets_addition_flag,
apid.program_update_date,
apid.program_application_id,
apid.program_id,
apid.request_id,
apid.asset_book_type_code,
fmag.book_type_code
FROM ap_invoice_distributions_all apid,
fa_mass_additions_gt fmag
WHERE apid.invoice_distribution_id = fmag.invoice_distribution_id
AND fmag.line_status = 'PROCESSED'
AND fmag.ledger_id = l_ledger_id
AND apid.assets_addition_flag = 'U') sq
SET sq.assets_addition_flag = 'Y',
sq.program_update_date = sysdate,
sq.program_application_id = fnd_global.prog_appl_id,
sq.program_id = fnd_global.conc_program_id,
sq.request_id = fnd_global.conc_request_id,
sq.asset_book_type_code = sq.book_type_code;
'No of Invoice Distribution Record Updated '
||'after successfully transferred to Asset: '
||TO_CHAR(l_count));
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 APID.assets_addition_flag = 'U';
'No of Invoice Distribution 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; -- Mass Record Inserted
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_total1));
'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 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 Discount 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