[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT cql.inventory_item_id item_id,
cql.cost_group_id cg_id,
cql.layer_id layer_id
FROM cst_quantity_layers cql
WHERE NVL(cql.layer_quantity,0) > 0
AND cql.organization_id = l_org_id
AND ( p_item_option = 1
OR (p_item_option = 2
AND cql.inventory_item_id = p_specific_item_id
)
OR (p_item_option = 5
AND EXISTS
( SELECT 'X'
FROM mtl_item_categories mic
WHERE mic.organization_id =
cql.organization_id
AND mic.category_id =
p_category_id
AND mic.category_set_id =
p_category_set_id
AND mic.inventory_item_id =
cql.inventory_item_id
)
)
)
AND EXISTS
( SELECT 'X'
FROM ap_invoice_distributions_all aida,
po_distributions_all pda,
po_line_locations_all plla,
po_lines_all pla
WHERE aida.po_distribution_id = pda.po_distribution_id
AND aida.posted_flag = 'Y'
--AND NVL(aida.reversal_flag,'N') <> 'Y'
AND aida.accounting_date <= l_cutoff_date
AND aida.inventory_transfer_status = 'N'
AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL')
AND pda.destination_type_code = 'INVENTORY'
AND pda.destination_organization_id =
l_org_id
AND plla.line_location_id = pda.line_location_id
AND pla.po_line_id = plla.po_line_id
AND pla.item_id = cql.inventory_item_id
AND (
( p_invoice_project_option = 1
AND pda.project_id IS NULL
AND cql.cost_group_id = l_default_cost_group_id)
OR
( pda.project_id IS NOT NULL
AND EXISTS
(SELECT 'X'
FROM pjm_project_parameters ppp
WHERE ppp.organization_id = l_org_id
AND ppp.costing_group_id =
cql.cost_group_id
AND ppp.project_id = pda.project_id
AND ppp.project_id =
decode(p_invoice_project_option,
1, ppp.project_id,
p_project_id)
)
)
)
-- J Changes ----------------------------------------------------------------
-- AND aida.root_distribution_id IS NULL
------------------------------------------------------------------------------
/* Invoice Lines Project: root_distribution_id does not exist, replaced with corrected_invoice_dist_id */
AND aida.corrected_invoice_dist_id IS NULL
);
SELECT default_cost_group_id
, nvl(process_enabled_flag,'N')
, organization_code
INTO l_default_cost_group_id
, l_process_enabled_flag
, l_organization_code
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT fcr.request_id
INTO l_dummy
FROM fnd_concurrent_requests fcr
WHERE program_application_id = 702
AND concurrent_program_id = l_conc_program_id
AND phase_code IN ('I','P','R')
AND argument1 = TO_CHAR(p_organization_id)
AND ( (argument3 IN ('1', '5') OR p_item_option IN (1,5))
OR (argument3 = '2'
AND argument6 = TO_CHAR(p_specific_item_id)
AND (argument11 = '1' OR p_invoice_project_option = 1)
)
OR (argument3 = '2'
AND argument6 = TO_CHAR(p_specific_item_id)
AND argument11 = '2'
AND argument13 = TO_CHAR(p_project_id)
)
)
AND fcr.request_id <> l_request_id
AND ROWNUM=1;
SELECT mp.primary_cost_method
INTO l_cost_method
FROM mtl_parameters mp
WHERE mp.organization_id = l_org_id;
UPDATE ap_invoice_distributions_all aida
SET aida.inventory_transfer_status = NULL
WHERE po_distribution_id IS NOT NULL
/*AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX') */
/*Bug 9823230: Commented as per internal discussion to enhance performance and set transfer status
to null for all LINE_TYPE_LOOKUP_CODE not eligible for IPV transfer */
AND aida.inventory_transfer_status = 'N'
AND aida.posted_flag = 'Y' --BUG#5709567-FPBUG#5109100
AND aida.accounting_date <= l_cutoff_date
AND NOT EXISTS
(
SELECT 'X'
FROM ap_invoice_distributions_all aida2
WHERE
(
(
aida2.line_type_lookup_code = 'IPV'
/* Start of bug 8270017 */
AND (
(aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
or
(aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
)
/* End of bug 8270017 */
)
OR /* Start of Bug 8681379*/
(
aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
and aida.invoice_id = aida2.invoice_id
and (aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
OR
aida.invoice_distribution_id = aida2.related_id /*added condition bug 8681379*/
)
)
)
);
SELECT cst_ap_variance_batches_s.nextval
INTO l_batch_id
FROM DUAL;
INSERT INTO cst_ap_variance_batches
( batch_id,
description,
organization_id,
item_option,
invoice_project_option,
adjustment_account,
cutoff_date,
transaction_process_mode,
specific_item_id,
specific_project_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
VALUES
( l_batch_id,
p_description,
p_organization_id,
P_item_option,
p_invoice_project_option,
p_adj_account,
l_cutoff_date,
P_transaction_process_mode,
p_specific_item_id,
p_project_id,
SYSDATE,
SYSDATE,
l_user_id,
l_user_id,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE,
l_login_id
);
INSERT INTO mtl_transactions_interface
(
transaction_interface_id,
source_code,
source_line_id, -- cavh.variance_header_id
source_header_id, -- cavh.batch_id
process_flag,
transaction_mode,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
validation_required,
inventory_item_id,
organization_id,
cost_group_id,
transaction_date,
transaction_quantity,
transaction_uom,
transaction_type_id,
value_change,
material_account,
transaction_reference -- cavh.po_distribution_id
)
(
SELECT mtl_material_transactions_s.nextval,
'VARIANCE TRF',
cavh.variance_header_id,
cavh.batch_id,
p_transaction_process_mode,
3,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_request_id,
l_prog_app_id,
l_prog_id,
1,
cavh.inventory_item_id,
cavh.organization_id,
cavh.cost_group_id,
decode(l_txn_date_profile, 2, SYSDATE,
cavh.transaction_date),
0,
msi.primary_uom_code,
80,
cavh.var_amount,
p_adj_account,
'PO Distribution: '|| TO_CHAR (cavh.po_distribution_id)
FROM cst_ap_variance_headers cavh,
mtl_system_items msi
WHERE cavh.batch_id = l_batch_id
AND cavh.var_amount <> 0
AND cavh.inventory_item_id = msi.inventory_item_id
AND cavh.organization_id = msi.organization_id
);
||' Rows inserted into MTI');
INSERT INTO mtl_txn_cost_det_interface
( transaction_interface_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
organization_id,
cost_element_id,
level_type,
value_change
)
(SELECT mti.transaction_interface_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
l_request_id,
l_prog_app_id,
l_prog_id,
mti.organization_id,
1, -- cost element id = 1
1, -- this level = 1
mti.value_change
FROM mtl_transactions_interface mti
WHERE mti.source_header_id = l_batch_id
);
||' Rows inserted into MTCDI');
| 1: Real transfer, update AP table to mark transferred invoices |
| 2: Simulated transfer, does not update AP tables |
| |
| aida.inventory_transfer_status: |
| N: Not transferred |
| Null: Transferred or Not Applicable |
| |
*----------------------------------------------------------------------------*/
FUNCTION trf_invoice_to_wip(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_organization_id IN NUMBER,
p_description IN VARCHAR2 DEFAULT NULL,
p_work_order_id IN NUMBER DEFAULT NULL,
p_item_type IN NUMBER,
p_item_option IN NUMBER DEFAULT NULL,
p_specific_item_id IN NUMBER DEFAULT NULL,
p_category_set_id IN NUMBER DEFAULT NULL,
p_category_id IN NUMBER DEFAULT NULL,
p_project_id IN NUMBER DEFAULT NULL,
p_adj_account IN NUMBER,
p_cutoff_date IN VARCHAR2,
p_transaction_process_mode IN NUMBER,
p_request_id IN NUMBER,
p_user_id IN NUMBER,
p_login_id IN NUMBER,
p_prog_appl_id IN NUMBER,
p_prog_id IN NUMBER
)
RETURN NUMBER IS
l_cutoff_date DATE;
SELECT DISTINCT
pda.po_distribution_id,
pla.item_id inventory_item_id,
pda.project_id
FROM po_distributions_all pda,
po_line_locations_all plla,
po_lines_all pla,
wip_entities we,
wip_discrete_jobs wdj
WHERE ( ( ( p_item_type = 1 -- OSP and direct
OR p_item_type = 2) -- OSP only
AND ( ( l_item_option = 1 -- All items
AND EXISTS (
SELECT 'X'
FROM mtl_system_items_b msi
WHERE msi.organization_id = p_organization_id
AND msi.inventory_item_id = pla.item_id
AND msi.outside_operation_flag = 'Y' and rownum <2) )
OR ( l_item_option = 2 -- Specific item
AND EXISTS (
SELECT 'X'
FROM mtl_system_items_b msi
WHERE msi.organization_id = p_organization_id
AND msi.inventory_item_id = p_specific_item_id
AND msi.inventory_item_id = pla.item_id
AND msi.outside_operation_flag = 'Y' and rownum <2 ) )
OR ( l_item_option = 5 -- Category items
AND EXISTS (
SELECT 'X'
FROM mtl_item_categories mic,
mtl_system_items_b msi
WHERE mic.organization_id = p_organization_id
AND mic.category_id = p_category_id
AND mic.category_set_id = p_category_set_id
AND mic.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = p_organization_id
AND msi.inventory_item_id = pla.item_id
AND msi.outside_operation_flag = 'Y' and rownum <2 ) )))
OR ( ( p_item_type = 1 -- OSP and direct
OR p_item_type = 3) -- direct only
AND ( pla.item_id IS NULL
OR EXISTS (
SELECT 'X'
FROM mtl_system_items_b msi
WHERE msi.organization_id = p_organization_id
AND msi.inventory_item_id = pla.item_id
AND msi.stock_enabled_flag = 'N'
)
)
)
)
AND plla.po_line_id = pla.po_line_id
AND pda.line_location_id = plla.line_location_id
AND pda.destination_type_code = 'SHOP FLOOR'
AND pda.destination_organization_id = p_organization_id
AND pda.wip_entity_id = nvl(p_work_order_id,pda.wip_entity_id)
AND we.wip_entity_id = pda.wip_entity_id
AND we.entity_type = 6 -- open maintenance work order
AND wdj.wip_entity_id = pda.wip_entity_id
AND wdj.status_type in (3,4) -- released / completed work order
AND ( ( pda.project_id IS NULL
AND l_project_option = 1)
OR ( pda.project_id IS NOT NULL
AND EXISTS (
SELECT 'X'
FROM pjm_project_parameters ppp
WHERE ppp.organization_id = p_organization_id
AND ppp.project_id = pda.project_id
AND ppp.project_id = decode(
l_project_option,
1,
ppp.project_id,
p_project_id)
and rownum <2) ))
AND EXISTS (
SELECT 'X'
FROM ap_invoice_distributions_all aida
WHERE aida.po_distribution_id = pda.po_distribution_id
AND aida.posted_flag = 'Y'
AND aida.accounting_date < l_cutoff_date
AND aida.inventory_transfer_status = 'N'
AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL') --same change as earlier
-- J Changes -----------------------------------------------------------------
-- AND aida.root_distribution_id IS NULL
-------------------------------------------------------------------------------
AND aida.corrected_invoice_dist_id IS NULL --same change as earlier
and rownum <2 ) ;
SELECT pda.po_distribution_id
FROM po_distributions_all pda,
wip_entities we,
wip_discrete_jobs wdj,
wip_operation_resources wor
WHERE we.wip_entity_id = pda.wip_entity_id
AND we.entity_type = 6
AND wdj.wip_entity_id = pda.wip_entity_id
AND wdj.status_type = 3
AND wor.wip_entity_id = pda.wip_entity_id
AND wor.operation_seq_num = pda.wip_operation_seq_num
AND wor.resource_seq_num = pda.wip_resource_seq_num
AND wor.standard_rate_flag = 1;
SELECT fcr.request_id
INTO l_dummy
FROM fnd_concurrent_requests fcr
WHERE program_application_id = 702
AND concurrent_program_id = l_conc_program_id
AND phase_code IN ('I','P','R')
AND argument2 = TO_CHAR(p_organization_id)
AND ( argument6 IN ('1', '5')
OR l_item_option IN (1,5)
OR ( argument6 = '2'
AND argument9 = TO_CHAR(p_specific_item_id)
AND ( argument14 = '1'
OR l_project_option = 1
OR (argument16 = TO_CHAR(p_project_id)))))
AND fcr.request_id <> l_request_id
AND ROWNUM=1;
UPDATE ap_invoice_distributions_all aida
SET aida.inventory_transfer_status = 'S'
WHERE aida.inventory_transfer_status = 'N'
AND aida.posted_flag = 'Y'
AND aida.accounting_date <= p_cutoff_date
AND aida.po_distribution_id = l_po_dists_tab(i);
UPDATE ap_invoice_distributions_all aida
SET aida.inventory_transfer_status = NULL
WHERE po_distribution_id IS NOT NULL
/*AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX') */
/* Bug 9823230: Commented as per internal discussion to enhance performance and set transfer status
to null for all LINE_TYPE_LOOKUP_CODE not eligible for IPV transfer*/
AND aida.inventory_transfer_status = 'N'
AND aida.posted_flag = 'Y'
AND aida.accounting_date <= l_cutoff_date
AND NOT EXISTS --same change as earlier
(
SELECT 'X'
FROM ap_invoice_distributions_all aida2
WHERE
(
(
aida2.line_type_lookup_code = 'IPV'
/* Start of bug 8270017 */
AND
(
(aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
or
(aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
)
/* End of bug 8270017 */
)
OR /* Start of Bug 8681379*/
(
aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
and aida.invoice_id = aida2.invoice_id
and (aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
OR
aida.invoice_distribution_id = aida2.related_id /*added condition bug 8681379*/
)
) /*End of Bug 8681379*/
)
);
SELECT cst_ap_variance_batches_s.nextval
INTO l_batch_id
FROM DUAL;
INSERT INTO
cst_ap_variance_batches
(
batch_id,
organization_id,
item_option,
invoice_project_option,
adjustment_account,
cutoff_date,
transaction_process_mode,
specific_item_id,
specific_project_id,
category_id,
category_set_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login,
description,
wip_entity_id,
item_type)
VALUES (
l_batch_id,
p_organization_id,
l_item_option,
l_project_option,
p_adj_account,
l_cutoff_date,
p_transaction_process_mode,
p_specific_item_id,
p_project_id,
p_category_id,
p_category_set_id,
SYSDATE,
SYSDATE,
l_user_id,
l_user_id,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE,
l_login_id,
p_description,
p_work_order_id,
p_item_type);
/* select legal entity from HR_ORGANIZATION_INFORMATION instead of cst_organization_definitions
for performance improvement */
SELECT org_information2
INTO l_legal_entity
FROM HR_ORGANIZATION_INFORMATION
where ORG_INFORMATION_CONTEXT = 'Accounting Information'
and organization_id = p_organization_id;
INSERT INTO
wip_cost_txn_interface (
transaction_id,
last_update_date,
last_updated_by,
last_updated_by_name,
creation_date,
created_by,
created_by_name,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
source_code,
source_line_id,
process_phase,
process_status,
transaction_type,
organization_id,
organization_code,
wip_entity_id,
entity_type,
primary_item_id,
transaction_date,
acct_period_id,
operation_seq_num,
resource_seq_num,
department_id,
resource_id,
usage_rate_or_amount,
basis_type,
autocharge_type,
standard_rate_flag,
transaction_quantity,
transaction_uom,
primary_quantity,
primary_uom,
actual_resource_rate,
reference,
po_header_id,
po_line_id,
receiving_account_id)
(
SELECT NULL,
SYSDATE,
l_user_id,
fu.user_name,
SYSDATE,
l_user_id,
fu.user_name,
l_login_id,
l_request_id,
l_prog_app_id,
l_prog_id,
SYSDATE,
'IPV',
cavh.variance_header_id,
decode(nvl(wor.resource_id,-1),-1,2,1),
1,
decode(nvl(wor.resource_id,-1),-1,17,3),
cavh.organization_id,
mp.organization_code,
pda.wip_entity_id,
6, -- Open Maintenance Job
cavh.inventory_item_id,
decode(l_txn_date_profile,2,SYSDATE,cavh.transaction_date),
oap.acct_period_id,
pda.wip_operation_seq_num,
pda.wip_resource_seq_num,
wor.department_id,
wor.resource_id,
cavh.var_amount,
wor.basis_type,
wor.autocharge_type,
2, -- Standard Rate Flag
0, -- Transaction Quantity
wor.uom_code,
0, -- Primary Quantity
wor.uom_code,
cavh.var_amount,
'PO Distribution: '|| TO_CHAR (cavh.po_distribution_id),
pda.po_header_id,
pda.po_line_id,
p_adj_account
FROM cst_ap_variance_headers cavh,
po_distributions_all pda,
wip_operation_resources wor,
org_acct_periods oap,
mtl_parameters mp,
fnd_user fu
WHERE cavh.batch_id = l_batch_id
AND cavh.var_amount <> 0
AND pda.po_distribution_id = cavh.po_distribution_id
AND wor.wip_entity_id (+) = pda.wip_entity_id
AND wor.operation_seq_num (+) = pda.wip_operation_seq_num
AND wor.resource_seq_num (+) = pda.wip_resource_seq_num
AND oap.organization_id = cavh.organization_id
AND decode(l_txn_date_profile,2,SYSDATE,cavh.transaction_date)
BETWEEN (oap.period_start_date + l_server_le_offset)
AND (oap.schedule_close_date+.99999 + l_server_le_offset)
AND mp.organization_id = cavh.organization_id
AND fu.user_id = l_user_id);
||' Rows inserted into WCTI');
SELECT nvl(process_enabled_flag,'N'), organization_code
INTO l_process_enabled_flag, l_organization_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT cavl.po_distribution_id,
SUM(NVL(cavl.var_amount,0)) var_amount
FROM cst_ap_variance_lines cavl
WHERE cavl.batch_id = p_batch_id
AND cavl.cost_group_id = p_cost_group_id
AND cavl.inventory_item_id = p_inventory_item_id
GROUP BY cavl.po_distribution_id;
select default_cost_group_id
into l_default_cost_group_id
from mtl_parameters
where organization_id = p_organization_id;
INSERT INTO cst_ap_variance_lines
(
variance_header_id,
variance_line_id,
batch_id,
invoice_distribution_id,
invoice_id,
distribution_line_number,
po_distribution_id,
invoice_price_variance,
base_invoice_price_variance,
var_amount,
project_id,
organization_id,
inventory_item_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login,
cost_group_id
)
(
SELECT
-1 variance_header_id,
cst_ap_variance_lines_s.nextval variance_line_id,
p_batch_id batch_id,
aida.invoice_distribution_id
invoice_distribution_id,
aida.invoice_id invoice_id,
aida.distribution_line_number
distribution_line_number,
aida.po_distribution_id po_distribution_id,
aida2.amount invoice_price_variance,
aida2.base_amount base_invoice_price_variance,
NVL(aida2.base_amount,0) var_amount,
pda.project_id project_id,
p_organization_id organization_id,
p_inventory_item_id inventory_item_id,
SYSDATE creation_date,
SYSDATE last_updated_date,
p_user_id last_updated_by,
p_user_id created_by,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id,
p_cost_group_id
FROM ap_invoice_distributions_all aida,
ap_invoice_distributions_all aida2,
po_distributions pda
WHERE aida.posted_flag = 'Y'
AND aida.accounting_date <= p_cutoff_date
AND aida2.posted_flag = 'Y'
AND aida2.accounting_date <= p_cutoff_date
AND aida.inventory_transfer_status = 'N'
/* Start of bug 8270017 */
AND (
(
aida2.line_type_lookup_code IN ('IPV')
and
(
(aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
or
(aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
)
)
OR /*Start of bug 8681379 */
(
aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
and aida.invoice_id = aida2.invoice_id
and aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
)
/*End of bug 8681379 */
)
/* End of bug 8270017 */
/*Added NONREC_TAX and other tax component 'TIPV','TERV','TRV' for bug 8681379 */
AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX')
AND aida2.line_type_lookup_code IN ('IPV','TIPV','TERV','TRV')
AND pda.po_distribution_id = aida.po_distribution_id
AND (
( p_invoice_project_option = 1
AND pda.project_id IS NULL
AND p_cost_group_id = l_default_cost_group_id)
OR
( pda.project_id IS NOT NULL
AND EXISTS
(SELECT 'X'
FROM pjm_project_parameters ppp
WHERE ppp.organization_id = p_organization_id
AND ppp.costing_group_id = p_cost_group_id
AND ppp.project_id = pda.project_id
AND ppp.project_id =
decode(p_invoice_project_option,
1, ppp.project_id,
p_project_id)
)
)
)
AND aida.po_distribution_id IS NOT NULL
-- bug3673238 -------------------------------------------------------
AND pda.destination_organization_id = p_organization_id
AND pda.destination_type_code = 'INVENTORY'
/* changes for performance improvement bug4873742 */
AND EXISTS (
SELECT 'X'
FROM po_line_locations_all plla,
po_lines_all pla
WHERE pla.po_line_id = plla.po_line_id
AND pla.item_id = p_inventory_item_id
AND nvl(plla.lcm_flag,'N') = 'N'
AND pda.line_location_id = plla.line_location_id)
-- end bug3673238 ---------------------------------------------------
-- J Changes ----------------------------------------------------------------
-- AND aida.root_distribution_id IS NULL
------------------------------------------------------------------------------
AND aida.corrected_invoice_dist_id IS NULL --same as change made earlier
);
|| ' Rows inserted into CAVL');
INSERT INTO cst_ap_variance_headers
(
variance_header_id,
po_distribution_id,
var_amount,
organization_id,
inventory_item_id,
cost_group_id,
transaction_date,
batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login
)
(
SELECT cst_ap_variance_headers_s.nextval, -- header_id
c_cavl_po_rec.po_distribution_id, -- po_dist
c_cavl_po_rec.var_amount, -- var_amount
p_organization_id,
p_inventory_item_id,
p_cost_group_id,
l_txn_date, -- txn_date
p_batch_id,
SYSDATE,
-1,
SYSDATE,
-1,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id
FROM DUAL
);
-- Update the ipv header id of all the detail lines
---------------------------------------------------------------------
l_stmt_num := 30;
UPDATE cst_ap_variance_lines cavl
SET cavl.variance_header_id =
(SELECT cavh.variance_header_id
FROM cst_ap_variance_headers cavh
WHERE cavh.batch_id = cavl.batch_id
AND cavh.po_distribution_id = cavl.po_distribution_id
AND cavh.cost_group_id = cavl.cost_group_id
)
WHERE cavl.batch_id = p_batch_id
AND EXISTS
( SELECT 'X'
FROM cst_ap_variance_headers cavh2
WHERE cavh2.batch_id = p_batch_id
AND cavh2.po_distribution_id = cavl.po_distribution_id
AND cavh2.cost_group_id = cavl.cost_group_id
);
UPDATE ap_invoice_distributions_all aida
SET aida.inventory_transfer_status = NULL
WHERE aida.inventory_transfer_status = 'N' --Perf Bug 1866130
-- Line below is not needed because cst_ap_variance_lines won't have null IPV
--AND NVL(aida.base_invoice_price_variance,0) <> 0
AND EXISTS
( SELECT 'X'
FROM cst_ap_variance_lines cavl
WHERE cavl.batch_id = p_batch_id
AND cavl.invoice_distribution_id =
aida.invoice_distribution_id
AND cavl.cost_group_id = p_cost_group_id
AND cavl.inventory_item_id = p_inventory_item_id
);
INSERT INTO
cst_ap_variance_lines (
variance_header_id,
variance_line_id,
batch_id,
invoice_distribution_id,
invoice_id,
distribution_line_number,
po_distribution_id,
invoice_price_variance,
base_invoice_price_variance,
var_amount,
project_id,
organization_id,
inventory_item_id,
creation_date,
last_update_date,
last_updated_by,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login,
cost_group_id)
(
SELECT -1,
cst_ap_variance_lines_s.nextval,
p_batch_id,
aida.invoice_distribution_id,
aida.invoice_id,
aida.distribution_line_number,
aida.po_distribution_id,
aida2.amount,
aida2.base_amount,
NVL(aida2.base_amount,0),
p_project_id,
p_organization_id,
nvl(p_inventory_item_id,-1),
SYSDATE,
SYSDATE,
p_user_id,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id,
NULL
FROM ap_invoice_distributions_all aida,
ap_invoice_distributions_all aida2
WHERE aida.posted_flag = 'Y'
AND aida.accounting_date < p_cutoff_date
AND aida2.posted_flag = 'Y'
AND aida2.accounting_date < p_cutoff_date
AND aida.inventory_transfer_status = 'N'
/* Start of bug 8270017 */
AND
(
(
aida2.line_type_lookup_code IN ('IPV')
and
(
(aida.invoice_id = aida2.invoice_id and aida.invoice_distribution_id = aida2.related_id)
or
(aida.invoice_distribution_id = aida2.corrected_invoice_dist_id)
)
)
OR /* Start of bug 8681379 */
(
aida2.line_type_lookup_code IN ('TIPV','TERV','TRV')
and aida.invoice_id = aida2.invoice_id
and aida.invoice_distribution_id = aida2.charge_applicable_to_dist_id
)
/* End of bug 8681379 */
)
/* End of bug 8270017 */
/*Added NONREC_TAX and other tax component 'TIPV','TERV','TRV' for bug 8681379 */
AND aida.line_type_lookup_code IN ('ITEM','ACCRUAL','NONREC_TAX')
AND aida2.line_type_lookup_code IN ('IPV','TIPV','TERV','TRV')
/* Ensure that Price Correction Invoices are not picked up */
-- AND aida.root_distribution_id IS NULL
AND aida.corrected_invoice_dist_id IS NULL
AND aida.po_distribution_id = p_po_distribution_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)|| ' Rows inserted into CAVL');
SELECT SUM(NVL(cavl.var_amount,0))
INTO l_var_amount
FROM cst_ap_variance_lines cavl
WHERE cavl.batch_id = p_batch_id
AND cavl.po_distribution_id = p_po_distribution_id;
SELECT cst_ap_variance_headers_s.nextval
INTO l_header_id
FROM dual;
INSERT INTO
cst_ap_variance_headers(
variance_header_id,
po_distribution_id,
var_amount,
organization_id,
inventory_item_id,
cost_group_id,
transaction_date,
batch_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
request_id,
program_application_id,
program_id,
program_update_date,
last_update_login)
VALUES ( l_header_id,
p_po_distribution_id,
l_var_amount,
p_organization_id,
p_inventory_item_id,
NULL,
l_txn_date,
p_batch_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_request_id,
p_prog_app_id,
p_prog_id,
SYSDATE,
p_login_id);
-- Update the ipv header id of all the detail lines
---------------------------------------------------------------------
l_stmt_num := 30;
UPDATE cst_ap_variance_lines cavl
SET cavl.variance_header_id = l_header_id
WHERE cavl.batch_id = p_batch_id
AND cavl.po_distribution_id = p_po_distribution_id;
UPDATE ap_invoice_distributions_all aida
SET aida.inventory_transfer_status = NULL
WHERE aida.inventory_transfer_status = 'N' --Perf Bug 1866130
-- AND NVL(aida.base_invoice_price_variance,0) <> 0 Not needed
AND aida.posted_flag = 'Y'
AND EXISTS
( SELECT 'X'
FROM cst_ap_variance_lines cavl
WHERE cavl.batch_id = p_batch_id
AND cavl.invoice_distribution_id = aida.invoice_distribution_id
);
SELECT MAX(
decode(
trunc(rt.transaction_date),
trunc(wdj.date_released),
wdj.date_released+0.00001,
rt.transaction_date))
INTO l_transaction_date
FROM rcv_transactions rt,
wip_discrete_jobs wdj
WHERE rt.wip_entity_id = wdj.wip_entity_id (+)
AND rt.transaction_type = 'DELIVER'
AND rt.po_distribution_id = p_po_distribution_id
AND rt.transaction_date = (
SELECT MAX(rt.transaction_date)
FROM rcv_transactions rt
WHERE rt.transaction_type = 'DELIVER'
AND rt.po_distribution_id = p_po_distribution_id);
SELECT MAX(
decode(
trunc(rt.transaction_date),
trunc(wdj.date_released),
wdj.date_released+0.00001,
rt.transaction_date))
INTO l_transaction_date
FROM rcv_transactions rt,
po_distributions_all pda,
wip_discrete_jobs wdj
WHERE pda.wip_entity_id = wdj.wip_entity_id (+)
AND pda.po_distribution_id = p_po_distribution_id
AND rt.transaction_type = 'RECEIVE'
AND ( rt.po_distribution_id = p_po_distribution_id
OR ( rt.po_line_location_id = pda.line_location_id
))
AND rt.transaction_date = (
SELECT MAX(rt.transaction_date)
FROM rcv_transactions rt,
po_distributions_all pda
WHERE rt.transaction_type = 'RECEIVE'
AND pda.po_distribution_id = p_po_distribution_id
AND ( rt.po_distribution_id = p_po_distribution_id /* bug 4137765 - for performance improvement */
OR ( rt.po_line_location_id = pda.line_location_id
)));
SELECT MIN(oap.period_start_date) ,
MAX(oap.schedule_close_date)+.99999
INTO l_first_date ,
l_last_date
FROM org_acct_periods oap
WHERE oap.organization_id = p_organization_id
AND oap.open_flag = 'Y'
AND oap.period_close_date is NULL;
/* select legal entity from HR_ORGANIZATION_INFORMATION instead of cst_organization_definitions
for performance improvement */
SELECT org_information2
INTO l_legal_entity
FROM HR_ORGANIZATION_INFORMATION
where ORG_INFORMATION_CONTEXT = 'Accounting Information'
and organization_id = p_organization_id;
SELECT MIN(oap.period_start_date) ,
MAX(oap.schedule_close_date)+(1-1/86400) -- +.99999 BUG#5709567-FPBIG#5109100
--Bug #13075737, Release commented code and modify to +(1-1/86400)
INTO l_first_date ,
l_last_date
FROM org_acct_periods oap
WHERE oap.organization_id = p_organization_id
AND oap.open_flag = 'Y'
AND oap.period_close_date is NULL;
/* select legal entity from HR_ORGANIZATION_INFORMATION instead of cst_organization_definitions
for performance improvement */
SELECT org_information2
INTO l_legal_entity
FROM HR_ORGANIZATION_INFORMATION
where ORG_INFORMATION_CONTEXT = 'Accounting Information'
and organization_id = p_organization_id;