[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Return : 'Y', if project information on the purchase order distribution can be updated.
'N', if project information on the purchase order distribution cannot be updated.
*/
FUNCTION Allow_Project_Info_Change ( p_po_distribution_id IN po_distributions_all.po_distribution_id%type)
RETURN varchar2 IS
l_sum_amount_interfaced number := 0;
select sum(nvl(entered_cr,0) - nvl(entered_dr,0))
into l_sum_amount_interfaced
from rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub
where rcv_txn.po_distribution_id = l_po_distribution_id
and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
and rcv_sub.pa_addition_flag in ('Y','I')
and ((rcv_txn.destination_type_code ='EXPENSE') OR
/*and ((rcv_txn.destination_type_code ='EXPENSE' AND rcv_txn.transaction_type <> 'RETURN TO RECEIVING') OR */--Bug4630478
(rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) );
select 1
into l_uninterfaced_to_pa
FROM dual
WHERE EXISTS
(SELECT 1 FROM rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub
,po_distributions_all podist/*Bug 3905697*/
where rcv_txn.po_distribution_id = l_po_distribution_id
and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
and ((rcv_txn.destination_type_code ='EXPENSE') OR
(rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
and rcv_sub.pa_addition_flag ='N');
select 1
into l_uninterfaced_to_pa
FROM dual
WHERE EXISTS
(SELECT 1 FROM rcv_transactions rcv_txn,
rcv_receiving_sub_ledger rcv_sub
,po_distributions_all podist/*Bug 3905697*/
where rcv_txn.po_distribution_id = l_po_distribution_id
and podist.po_distribution_id=rcv_txn.po_distribution_id/*Bug 3905697*/
and rcv_sub.code_combination_id = podist.code_combination_id/*Bug 3905697*/
and rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
and ((rcv_txn.destination_type_code ='EXPENSE') OR
(rcv_txn.destination_type_code='RECEIVING' AND (rcv_txn.transaction_type IN ('RETURN TO VENDOR','RETURN TO RECEIVING')) ) )
and rcv_sub.pa_addition_flag ='N');
/*This is a public API, which will update PA_ADDITION_FLAG in
rcv_receiving_sub_ledger table. This API will be called from
purchasing module at the time of receipt creation.*/
PROCEDURE Update_PA_Addition_Flg (p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
p_commit IN VARCHAR2 default FND_API.G_FALSE,
p_validation_level IN NUMBER default FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_rcv_transaction_id IN NUMBER,
p_po_distribution_id IN NUMBER,
p_accounting_event_id IN NUMBER)
IS
/*l_project_id po_distributions_all.project_id%type; Bug 5585218 */
SELECT /*+ leading(rcv_txn) index(rcvsub RCV_RECEIVING_SUB_LEDGER_N1) */ rcv_txn.transaction_id /*4338075*/
,rcv_txn.po_distribution_id
,rcvsub.accounting_event_id -- pricing changes
FROM rcv_transactions rcv_txn
,po_distributions podist
,rcv_receiving_sub_ledger rcvsub
WHERE rcv_txn.transaction_id = rcvsub.rcv_transaction_id
AND rcv_txn.parent_transaction_id = (SELECT parent_transaction_id
FROM rcv_transactions rcv_txn3
WHERE rcv_txn3.transaction_id = p_transaction_id)
and rcv_txn.po_distribution_id = podist.po_distribution_id
and podist.code_combination_id = rcvsub.code_combination_id
and rcvsub.actual_flag = 'A'
and podist.accrue_on_receipt_flag = 'Y'
/*and podist.project_id = p_project_id Bug 5585218 */
and rcvsub.pa_addition_flag = 'N' -- pricing changes
and ((rcv_txn.destination_type_code = 'EXPENSE' ) OR
(rcv_txn.destination_type_code = 'RECEIVING' AND
rcv_txn.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
))
and 0 = (SELECT /*+ INDEX(RCV_TXN2 RCV_TRANSACTIONS_N1) */sum(nvl(rcvsub2.entered_dr,0)-nvl(rcvsub2.entered_cr,0))/*4338075*/
FROM rcv_transactions rcv_txn2
,rcv_receiving_sub_ledger rcvsub2
,po_distributions podist2
WHERE rcv_txn2.transaction_id = rcvsub2.rcv_transaction_id
and podist2.po_distribution_id = rcv_txn2.po_distribution_id
and podist2.code_combination_id = rcvsub2.code_combination_id
and rcvsub2.actual_flag = 'A'
and rcv_txn2.parent_transaction_id = rcv_txn.parent_transaction_id
and rcvsub2.code_combination_id = rcvsub.code_combination_id
and trunc(rcv_txn2.transaction_date) = trunc(rcv_txn.transaction_date)
and rcvsub2.pa_addition_flag = 'N' -- pricing changes
and rcv_txn2.po_distribution_id = rcv_txn.po_distribution_id
and ((rcv_txn2.destination_type_code = 'EXPENSE' ) OR
(rcv_txn2.destination_type_code = 'RECEIVING' AND
rcv_txn2.transaction_type in ('RETURN TO VENDOR','RETURN TO RECEIVING')
))
);
l_rcv_txn_id_tbl.delete;
l_po_dist_id_tbl.delete;
l_rcv_acct_evt_tbl.delete; -- pricing changes
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = 'Z'
WHERE rcv_sub.rcv_transaction_id = l_rcv_txn_id_tbl(i) --pricing changes
AND rcv_sub.pa_addition_flag = 'N'
AND (rcv_sub.accounting_event_id = l_rcv_acct_evt_tbl(i) --pricing changes
OR rcv_sub.accounting_event_id IS NULL); --pricing changes
-- write_log (LOG,'Total number of transctions updated to Z:'||l_num_rows);
l_rcv_txn_id_tbl.delete;
l_po_dist_id_tbl.delete;
l_rcv_acct_evt_tbl.delete; -- pricing changes
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_addition_flag = NULL
WHERE rcv_sub.pa_addition_flag ='N'
AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
AND EXISTS
(
SELECT 'X'
FROM rcv_transactions rcv_txn
WHERE rcv_txn.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
AND ((rcv_txn.destination_type_code IN ('INVENTORY','MULTIPLE','SHOP FLOOR')
OR
(rcv_txn.destination_type_code = 'RECEIVING'
AND
(rcv_txn.transaction_type NOT IN ('RETURN TO VENDOR','RETURN TO RECEIVING')
)
)
)
OR
(EXISTS
(SELECT po_distribution_id
FROM po_distributions po_dist
WHERE po_dist.po_distribution_id = rcv_txn.po_distribution_id
AND ((rcv_txn.destination_type_code = 'EXPENSE' AND
po_dist.project_id IS NULL)
OR
(rcv_txn.destination_type_code = 'EXPENSE' AND
nvl(po_dist.project_id,0) > 0 AND
po_dist.accrue_on_receipt_flag = 'N')
OR
(rcv_txn.destination_type_code = 'RECEIVING' AND
po_dist.project_id IS NULL)
OR
(rcv_txn.destination_type_code = 'RECEIVING' AND
po_dist.project_id IS NOT NULL AND
po_dist.accrue_on_receipt_flag = 'N')
)
)
) OR
( pa_nl_installed.is_nl_installed = 'Y' --EIB trackable items
AND EXISTS (SELECT 'X'
FROM mtl_system_items si,
po_lines_all pol,
po_distributions_all po_dist1
WHERE po_dist1.po_line_id = pol.po_line_id
AND po_dist1.po_distribution_id = rcv_txn.po_distribution_id
AND si.inventory_item_id = pol.item_id
AND po_dist1.project_id IS NOT NULL
AND si.comms_nl_trackable_flag = 'Y')
) OR
(
rcv_sub.actual_flag <> 'A'
)
)
);
UPDATE rcv_receiving_sub_ledger rcv_sub
SET rcv_sub.pa_Addition_Flag = 'X'
WHERE rcv_sub.pa_addition_flag IN ('N','I')
AND rcv_sub.rcv_transaction_id = l_rcv_transaction_id
AND EXISTS
( SELECT po_dist.code_combination_id
FROM Rcv_Transactions rcv_txn, PO_Distributions po_dist
WHERE
(
(rcv_txn.destination_type_code ='EXPENSE' )
OR (rcv_txn.destination_type_code = 'RECEIVING'
AND (rcv_txn.transaction_type
IN ('RETURN TO VENDOR','RETURN TO RECEIVING')))
)
-- AND rcv_txn.transaction_date <= nvl(G_GL_DATE,rcv_txn.transaction_date)
AND rcv_txn.PO_DISTRIBUTION_ID = po_dist.po_distribution_id
AND rcv_sub.code_combination_id <> po_dist.code_combination_id
AND rcv_sub.rcv_transaction_id = rcv_txn.transaction_id
AND rcv_sub.actual_flag = 'A'
-- AND po_dist.expenditure_item_date <= nvl(G_TRANSACTION_DATE,po_dist.expenditure_item_date)
AND po_dist.project_ID > 0
AND po_dist.accrue_on_receipt_flag= 'Y') ;
END Update_PA_Addition_Flg;