[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(ppp.ipv_expenditure_type, pop.ipv_expenditure_type)
into l_ipv_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = X_Org_Id
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = X_Project_Id;
select nvl(ppp.erv_expenditure_type, pop.erv_expenditure_type)
into l_erv_expenditure_type
from pjm_project_parameters ppp
, pjm_org_parameters pop
where pop.organization_id = X_Org_Id
and ppp.organization_id (+) = pop.organization_id
and ppp.project_id (+) = X_Project_Id;
SELECT PJM_TASK_AUTO_ASSIGN.Inv_Task_WNPS
( POD.Destination_Organization_Id
, X_Project_Id
, POL.Item_Id
, POD.Po_Header_Id
, Null
, Null )
FROM PO_Distributions POD
, PO_Lines POL
WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
AND POL.PO_Line_Id = POD.PO_Line_Id;
SELECT PJM_TASK_AUTO_ASSIGN.WIP_Task_WNPS
( wo.organization_id
, X_Project_Id
, wo.standard_operation_id
, wdj.wip_entity_id
, wdj.primary_item_id
, wo.department_id )
FROM PO_Distributions POD
, WIP_Discrete_Jobs WDJ
, WIP_Operations WO
WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
AND WO.WIP_Entity_Id = POD.WIP_Entity_Id
AND WO.Operation_Seq_Num = POD.WIP_Operation_Seq_Num
AND WDJ.WIP_Entity_Id = WO.Wip_Entity_Id;
SELECT
INV.Invoice_id Invoice_Id
, DIST.Distribution_Line_Number Distribution_Line_Number
, PAP.Project_Id Project_Id
, PAP.Segment1 Project_Number
, POD.Task_id Task_Id
, DIST.Accounting_Date Expenditure_Item_Date
, INV.Vendor_Id Vendor_Id
, INV.Created_By Created_By
, POD.Destination_Organization_Id Expenditure_Organization_Id
, NVL( DIST.description
, POL.Item_Description) Expenditure_Comment
, NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
, DIST.Rate_Var_Code_Combination_Id Rate_Var_Code_Combination_Id
, DIST.Price_Var_Code_Combination_Id Price_Var_Code_Combination_Id
, nvl( DIST.Accts_Pay_Code_Combination_Id
, INV.Accts_Pay_Code_Combination_Id)
Accts_Pay_Code_Combination_Id
, INV.Invoice_Currency_Code Invoice_Currency_Code
, INV.Exchange_Rate_Type Exchange_Rate_Type
, INV.Exchange_Date Exchange_Date
, INV.Exchange_Rate Exchange_Rate
, DIST.Invoice_Price_Variance Invoice_Price_Variance
, DIST.Base_Invoice_Price_Variance Base_Invoice_Price_Variance
, DIST.Exchange_Rate_Variance Exchange_Rate_Variance
, POD.PO_Distribution_Id PO_Distribution_Id
, POD.Destination_Type_Code Destination_Type_Code
, DIST.RCV_Transaction_Id RCV_Transaction_Id
FROM
AP_Invoices INV,
AP_Invoice_Distributions DIST,
PO_Distributions POD,
PO_Lines POL,
PA_Projects_ALL PAP,
PJM_Org_Parameters POP
WHERE DIST.Pa_Addition_Flag IN ( 'N','S','A','B','C','D','E','I',
'J','K','M','P','Q','V','X','W' )
AND DIST.Posted_Flag = 'Y'
AND DIST.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
AND DIST.Invoice_Id = INV.Invoice_Id
AND (( l_Start_Date is null and l_End_Date is null)
OR ( l_Start_Date is not null and l_End_Date is not null
and DIST.Accounting_Date between l_Start_Date and l_End_Date)
OR ( l_Start_Date is not null and l_End_Date is null
and DIST.Accounting_Date >= l_Start_Date )
OR ( l_Start_Date is null and l_End_Date is not null
and DIST.Accounting_Date <= l_End_Date ))
AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
AND POD.Destination_Type_Code in ( 'INVENTORY' , 'SHOP FLOOR' )
AND POP.Organization_Id = POD.Destination_Organization_Id
AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
AND PAP.Project_Id = NVL(X_Project_Id, PAP.Project_Id)
AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
AND POD.Po_Line_Id = POL.Po_Line_Id
ORDER BY 9,1,2
for update;
select ap.base_currency_code
into l_base_currency_code
from gl_sets_of_books gl,
ap_system_parameters ap
where gl.set_of_books_id = ap.set_of_books_id;
UPDATE ap_invoice_distributions apd
SET apd.pa_addition_flag = 'Z'
WHERE apd.pa_addition_flag = 'S'
AND apd.request_id = l_request_id
AND 0 = (
SELECT SUM( nvl(apd2.base_amount , apd2.amount) )
FROM po_distributions pod
, ap_invoice_distributions apd2
WHERE pod.po_distribution_id = apd.po_distribution_id
AND apd2.po_distribution_id = pod.po_distribution_id
AND apd2.pa_addition_flag = apd.pa_addition_flag
AND apd2.request_id = apd.request_id
AND apd2.dist_code_combination_id = apd.dist_code_combination_id
AND apd2.invoice_id = apd.invoice_id
AND apd2.accounting_date = apd.accounting_date
);
SELECT rsh.receipt_num
INTO l_receipt_num
FROM rcv_shipment_headers rsh
, rcv_transactions rt
WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
AND rsh.shipment_header_id = rt.shipment_header_id;
SELECT User_Conversion_Type
INTO l_User_Conv_Type
FROM gl_daily_conversion_types
WHERE conversion_type = InvRec.Exchange_Rate_Type;
fnd_message.set_name('PJM','CONC-APINV Insert');
select NVL(pa_posting_flag,'N'),
NVL(pa_autoaccounting_flag,'N')
into l_blue_print_enabled_flag,
l_autoaccounting_flag
from pjm_org_parameters
where organization_id = InvRec.Expenditure_Organization_Id;
INSERT INTO pa_transaction_interface
(transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
expenditure_comment,
orig_transaction_reference,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
orig_exp_txn_reference1,
orig_exp_txn_reference2,
orig_exp_txn_reference3,
gl_date,
system_linkage,
transaction_status_code,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
acct_raw_cost,
acct_burdened_cost,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT
l_transaction_source
, l_Batch_Name
, pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_IPV_Exp_Type
, InvRec.PA_Quantity
, NVL( InvRec.Expenditure_Comment , l_IPV_Exp_Comment )
, DIST.Invoice_Distribution_Id
, 'Y'
, InvRec.Price_Var_Code_Combination_Id
, InvRec.Accts_Pay_Code_Combination_Id
, InvRec.PO_Distribution_Id
, InvRec.RCV_Transaction_Id
, l_receipt_num
, DIST.Accounting_Date
, 'INV'
, l_trx_status_code
, InvRec.Invoice_Currency_Code /* denom_currency_code */
, InvRec.Invoice_Price_Variance /* denom_raw_cost */
, InvRec.Invoice_Price_Variance /* denom_burdened_cost */
, InvRec.Exchange_Date /* acct_rate_date */
, l_User_Conv_Type /* acct_rate_type */
, InvRec.Exchange_Rate /* acct_exchange_rate */
, nvl(InvRec.Base_Invoice_Price_Variance,
InvRec.Invoice_Price_Variance) /* acct_raw_cost */
, nvl(InvRec.Base_Invoice_Price_Variance,
InvRec.Invoice_Price_Variance) /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
FROM
AP_Invoice_Distributions DIST
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
-- AND DIST.PA_Addition_Flag = 'S'
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
INSERT INTO pa_transaction_interface
(transaction_source,
batch_name,
expenditure_ending_date,
employee_number,
organization_name,
expenditure_item_date,
project_number,
task_number,
expenditure_type,
quantity,
expenditure_comment,
orig_transaction_reference,
unmatched_negative_txn_flag,
dr_code_combination_id,
cr_code_combination_id,
orig_exp_txn_reference1,
orig_exp_txn_reference2,
orig_exp_txn_reference3,
gl_date,
system_linkage,
transaction_status_code,
denom_currency_code,
denom_raw_cost,
denom_burdened_cost,
acct_rate_date,
acct_rate_type,
acct_exchange_rate,
acct_raw_cost,
acct_burdened_cost,
creation_date,
created_by,
last_update_date,
last_updated_by
)
SELECT
l_transaction_source
, l_Batch_Name
, pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
, NULL
, ORG.Name
, InvRec.Expenditure_Item_Date
, InvRec.Project_Number
, TASK.Task_Number
, l_ERV_Exp_Type
, InvRec.PA_Quantity
, NVL( InvRec.Expenditure_Comment , l_ERV_Exp_Comment )
, DIST.Invoice_Distribution_Id
, 'Y'
, InvRec.Rate_Var_Code_Combination_Id
, InvRec.Accts_Pay_Code_Combination_Id
, InvRec.PO_Distribution_Id
, InvRec.RCV_Transaction_Id
, l_receipt_num
, DIST.Accounting_Date
, 'INV'
, l_trx_status_code
, l_base_currency_code /* denom_currency_code */
, InvRec.Exchange_Rate_Variance /* denom_raw_cost */
, InvRec.Exchange_Rate_Variance /* denom_burdened_cost */
, NULL /* acct_rate_date */
, NULL /* acct_rate_type */
, NULL /* acct_exchange_rate */
, InvRec.Exchange_Rate_Variance /* acct_raw_cost */
, InvRec.Exchange_Rate_Variance /* acct_burdened_cost */
, SYSDATE
, l_user_id
, SYSDATE
, l_user_id
FROM
AP_Invoice_Distributions DIST
, PA_Tasks TASK
, HR_Organization_Units ORG
WHERE
DIST.Invoice_Id = InvRec.Invoice_Id
AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
-- AND DIST.PA_Addition_Flag = 'S'
AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
AND TASK.Task_Id = l_Task_Id;
UPDATE AP_Invoice_Distributions
SET Pa_Addition_Flag = 'Y',
Request_Id = l_request_id
WHERE -- Pa_Addition_Flag = 'S' AND
Invoice_Id = InvRec.Invoice_Id
AND Distribution_Line_Number = InvRec.Distribution_Line_Number;