[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select hold_lookup_code from ap_holds_all
where invoice_id= p_invoice_id
and hold_lookup_code in ('Pay When Paid','PO Deliverable','Project Hold') --bug 9525493
and release_reason IS NULL
and hold_lookup_code not in (select decode(FND_PROFILE.value('PA_PAY_WHEN_PAID'),'N','Pay When Paid','Y','#######','Pay When Paid') from dual);
select hold_lookup_code from ap_holds_all
where invoice_id= p_invoice_id
and hold_lookup_code in ('Pay When Paid','PO Deliverable','Project Hold') --bug 9525493
and release_reason IS NULL
and hold_lookup_code = decode(p_hold_type,
'REL_PWP_HOLD','Pay When Paid',
'REL_DEL_HOLD','PO Deliverable',
'REL_PROJ_HOLD','Project Hold', --bug 9525493
null)
;
Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null
UNION ALL
Select hold_lookup_code, hold_reason, 'OTH' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code Not In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null;
Update PA_PWP_AP_INV_HDR Set RELHOLD_REJ_REASON = SubStr(l_err_msg,1,2000)
Where invoice_id = p_inv_tbl(Inv_RelHOld_rec);
Update PA_PWP_AP_INV_HDR Set RELHOLD_REJ_REASON = SubStr(l_err_msg,1,2000)
Where invoice_id = p_inv_tbl(Inv_RelHOld_rec);
Update PA_PWP_AP_INV_HDR Set HOLD_REASON = l_hold_reason1,
PWP_HOLD_FLAG = l_inv_pwp_hold,
DLV_HOLD_FLAG = l_inv_dlv_hold,
HOLD_FLAG = l_inv_hold,
HOLD_APPLIED_YN = l_hold_applied_yn
Where Invoice_Id = p_inv_tbl(Inv_RelHOld_rec)
And RELHOLD_REJ_REASON Is Null;
select apinv.invoice_id Invoice_Id,
apinv.invoice_num invoice_num,
vend.vendor_id vendor_Id,
vend.vendor_name Supplier_name,
vend.segment1 Supplier_Num,
apinv.Invoice_Date Invoice_Date,
P_project_id Project_Id,
apinv.invoice_Currency_code invoice_Currency,
apinv.payment_Currency_code Payment_Currency,
appay.payment_cross_rate Exchange_Rate, /* Bug# 8785535*/
(select vendor_site_code from
po_vendor_sites_all
where vendor_id = apinv.vendor_id
and vendor_site_id = apinv.vendor_site_id) Supplier_Site,
Invoice_Amount,
decode(apinv.payment_Currency_code, apinv.invoice_Currency_code,
sum(amount_remaining),
sum(amount_remaining)/nvl(appay.payment_cross_rate,1)) UnPaid_Inv_Amt, /* Bug# 8785535*/
(decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(gross_amount),
sum(gross_amount)/nvl(appay.payment_cross_rate,1)) - /* Bug# 8785535*/
decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(amount_remaining),
sum(amount_remaining)/nvl(appay.payment_cross_rate,1))) Paid_Inv_Amt, /* Bug# 8785535*/
apinv.invoice_Type_Lookup_code invoice_type, /*Added for bug 8293625 */
apinv.cancelled_date Cancelled_Date /*Added for bug 8293625 */
from ap_invoices_all apinv,
ap_payment_schedules_all appay,
po_vendors vend
where exIsts (select 1 from
ap_invoice_dIstributions_all apd
where apd.project_id = P_project_Id
and apd.posted_flag ='Y'
and apinv.invoice_id = apd.invoice_id)
and appay.invoice_id(+)=apinv.invoice_id
and vend.vendor_id = apinv.vendor_id
--and apinv.invoice_amount !=0 -- Bug# 7713608
and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
and apinv.invoice_id = NVL(G_Invoice_Id,apinv.invoice_id)
group by apinv.invoice_id, apinv.invoice_num,
vend.vendor_name,apinv.invoice_Date, apinv.invoice_Currency_code,
apinv.vendor_id,apinv.vendor_site_id, apinv.Invoice_Amount, vend.vendor_id,
apinv.payment_Currency_code,appay.payment_cross_rate,vend.segment1,
apinv.invoice_Type_Lookup_code, --Bug# 8717502
apinv.cancelled_date --Bug# 8717502
order by apinv.invoice_id;
select nvl(sum(apd.amount),0) InvRtngAmount from
ap_invoices_all aia,
ap_invoice_dIstributions_all apd
where aia.invoice_id = p_invoice_id
and nvl(aia.Cancelled_Date,Sysdate+1) = (Sysdate+1)
and apd.invoice_id = aia.invoice_id
and aia.invoice_type_lookup_code <> 'RETAINAGE RELEASE'
and apd.posted_flag = 'Y'
and apd.line_type_lookup_code = 'RETAINAGE'
group by apd.invoice_id;
select nvl((decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(gross_amount),
sum(gross_amount)/nvl(appay.payment_cross_rate,1)) -
decode(apinv.payment_Currency_code,
apinv.invoice_Currency_code,
sum(amount_remaining),
sum(amount_remaining)/nvl(appay.payment_cross_rate,1))),0) Paid_Inv_Amt
from ap_invoices_all apinv,
ap_payment_schedules_all appay
where apinv.invoice_id = p_invoice_id and exIsts (select 1 from
ap_invoice_dIstributions_all apd
where apd.project_id = P_project_Id
and apd.posted_flag ='Y'
and apinv.invoice_id = apd.invoice_id)
and appay.invoice_id(+)=apinv.invoice_id
and apinv.invoice_type_lookup_code <> 'EXPENSE REPORT'
and exists (
(SELECT 1
FROM ap_invoice_payments_all invpay ,
pa_projects_all proj,
pa_implementations_all imp,
GL_PERIOD_STATUSES glp
WHERE proj.project_id = p_project_id
and invpay.invoice_id = appay.invoice_id
and invpay.payment_num = appay.payment_num
and glp.application_id = 101
AND glp.adjustment_period_flag = 'N'
AND glp.set_of_books_id = imp.set_of_books_id --Bug# 7713608
AND glp.closing_status = 'O'
and imp.org_id = proj.org_id
GROUP BY glp.application_id,glp.adjustment_period_flag,glp.closing_status
HAVING max(invpay.accounting_date)
BETWEEN MAX(glp.start_date) AND MAX(glp.end_date)
)) group by apinv.payment_Currency_code,
apinv.invoice_Currency_code, appay.payment_cross_rate;
select apd.invoice_id,
apd.project_id,
apd.task_id,
apd.Expenditure_Item_Date,
pod.po_header_id,
--sum(apd.amount) ProjInvAmount,/*Commeneted this line for
/*bug#13614733*/
sum(nvl(rc_tax.amount,0)) rc_tax,
sum(ap_pay_hd.amount) Disc_Taken_On_Invoice /* Bug# 7833675 */
from
ap_invoice_dIstributions_all apd,
ap_invoice_dIstributions_all rc_tax,
po_dIstributions_all pod,
ap_payment_hist_dists ap_pay_hd
where apd.project_Id = p_project_Id
and rc_tax.line_type_lookup_code(+) = 'REC_TAX'
and rc_tax.tax_recoverable_flag(+) = 'Y'
and rc_tax.charge_applicable_to_dist_id(+)=apd.invoice_distribution_id
and rc_tax.posted_flag(+) = 'Y'
and apd.invoice_id = p_invoice_id
and rc_tax.invoice_id(+) = p_invoice_id
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and nvl(rc_tax.reversal_flag(+),'N') = 'N' /* Additional scenario mentioned by UMA */
and ap_pay_hd.invoice_distribution_id(+) = apd.invoice_distribution_id /* Bug# 7833675 */
and ap_pay_hd.pay_dist_lookup_code(+) = 'DISCOUNT' /* Bug# 7833675 */
-- and apd.line_type_lookup_code <> 'RETAINAGE' /* Bug# 8310848 */
group by apd.invoice_id, apd.project_id, apd.task_id, apd.Expenditure_Item_Date,
pod.po_header_id
order by apd.project_id, apd.task_id, apd.Expenditure_Item_Date, apd.invoice_id,pod.po_header_id;
select sum(apd.amount) ProjRtngAmount,
-- sum(retained_amount_remaining) Outstanding_Retained
sum(apd1.amount) RtngReleaseAmount
from
ap_invoices_all aia,
ap_invoice_dIstributions_all apd,
ap_invoice_dIstributions_all apd1, /* Bug# 8310848 */
po_dIstributions_all pod
where aia.invoice_id = p_invoice_id
and nvl(aia.Cancelled_Date,Sysdate+1) = (Sysdate+1)
and aia.invoice_type_lookup_code <> 'RETAINAGE RELEASE'
and apd.invoice_id = aia.invoice_id
and apd.project_Id = p_project_id
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and pod.po_header_id(+)=p_po_header_id
and apd.line_type_lookup_code = 'RETAINAGE'
and apd.task_id = p_task_id
and apd.expenditure_item_date = p_expenditure_item_date
and apd1.retained_invoice_dist_id(+) = apd.invoice_distribution_id /* Bug# 8310848 */
and apd1.project_id(+) = apd.project_id /* Bug# 8310848 */
and apd1.task_id(+) = apd.task_id /* Bug# 8310848 */
and apd1.expenditure_item_date(+) = apd.expenditure_item_date /* Bug# 8310848 */
and apd1.po_distribution_id(+) = apd.po_distribution_id /* Bug# 8310848 */
and nvl(apd1.reversal_flag(+),'N') = 'N' /* Bug# 8310848 */
and nvl(apd1.posted_flag(+), 'N') = 'Y' /* Bug# 8310848 */
group by apd.invoice_id, apd.project_Id, apd.task_id, apd.po_dIstribution_id, apd.expenditure_item_date;
Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null
UNION ALL
Select hold_lookup_code, hold_reason, 'OTH' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code Not In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null;
Select distinct draft_invoice_num,link_type From (
Select draft_invoice_num, 'M' link_type From PA_PWP_LINKED_INVOICES PWP
Where PWP.AP_INVOICE_ID = p_invoice_id
And PWP.PROJECT_ID = p_project_id
UNION ALL
Select pdii.draft_invoice_num, 'A' From PA_DRAFT_INVOICE_ITEMS PDII ,
PA_CUST_REV_DIST_LINES CRDL ,
PA_EXPENDITURE_ITEMS EI
Where PDII.project_id = crdl.project_id
And pdii.draft_invoice_num = crdl.draft_invoice_num
And pdii.line_num = crdl.draft_invoice_item_line_num
And crdl.expenditure_item_id = ei.expenditure_item_id
And ei.system_linkage_function = 'VI'
And ei.document_header_id =p_invoice_id
And ei.transaction_source like 'AP%'
And ei.project_id =p_project_id);
Select Segment1 PO_NUMBER From po_headers_all
Where po_header_id in (Select distinct po_header_id
from pa_pwp_ap_inv_dtl where invoice_id = p_Invoice_id);
Select distinct Invoice_Id From (
Select AP_Invoice_Id Invoice_Id From PA_PWP_LINKED_INVOICES PWP
Where PWP.draft_invoice_num = P_Draft_Inv_Num
And PWP.PROJECT_ID = p_project_id
And ap_Invoice_Id is not null
UNION ALL
Select ei.document_header_id From PA_DRAFT_INVOICE_ITEMS PDII ,
PA_CUST_REV_DIST_LINES CRDL ,
PA_EXPENDITURE_ITEMS EI
Where PDII.project_id = crdl.project_id
And pdii.draft_invoice_num = P_Draft_Inv_Num
AND pdii.draft_invoice_num = crdl.draft_invoice_num
And pdii.line_num = crdl.draft_invoice_item_line_num
And crdl.expenditure_item_id = ei.expenditure_item_id
And ei.system_linkage_function = 'VI'
And ei.transaction_source like 'AP%'
And ei.project_id =p_project_id);
select
sum(rc_tax.amount) ProjInv_RCTax_Amount
from
ap_invoice_dIstributions_all apd,
po_dIstributions_all pod,
ap_invoice_dIstributions_all rc_tax
where apd.project_Id = p_project_Id
and apd.invoice_id = p_invoice_id
and apd.task_id = p_task_id
and apd.expenditure_item_date = p_expenditure_item_date
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and pod.po_header_id(+) = p_po_header_id
and rc_tax.line_type_lookup_code = 'REC_TAX'
and rc_tax.tax_recoverable_flag = 'Y'
and rc_tax.charge_applicable_to_dist_id=apd.invoice_distribution_id
and rc_tax.posted_flag = 'Y'
and apd.line_type_lookup_code <> 'RETAINAGE'
group by apd.invoice_id, apd.project_id, apd.task_id, apd.Expenditure_Item_Date,
pod.po_header_id;
select app.Description,
app.GL_date,
(select meaning from FND_LOOKUP_VALUES where lookup_type = 'INVOICE TYPE'
and view_application_id =200 and language = USERENV('LANG') and lookup_code=app.invoice_type_lookup_code) invoice_type,
(select min(psd.due_date) from ap_payment_schedules_all psd where psd.invoice_id=app.invoice_id) earliest_due_date,
app.exchange_rate_type,
(select user_conversion_type from GL_DAILY_CONVERSION_TYPES where conversion_type=app.exchange_rate_type) exchange_rate_type1,-- Bug 8904838
app.exchange_date,
app.exchange_rate
from ap_invoices_all app where app.invoice_id=p_invoice_id
and
exists (select 1 from
ap_invoice_dIstributions_all apd
where apd.project_id = P_project_Id
and apd.posted_flag ='Y'
and apd.invoice_id=p_invoice_id);
select sum(dist.amount) prepaid_amount from ap_prepay_app_dists dist, ap_invoice_dIstributions_all apd
where dist.invoice_distribution_id = apd.invoice_distribution_id
and apd.project_id=P_Project_Id
and apd.invoice_id=p_invoice_id
group by apd.invoice_id;
Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id;
Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id;
Delete from PA_PWP_AP_INV_HDR where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
Delete from PA_PWP_AP_INV_DTL where project_id = P_Project_Id And Invoice_Id = G_Invoice_Id;
SELECT PA_PWP_AP_INV_HDR_S.nextval
INTO L_PA_PWP_AP_HDR_ID
FROM dual;
log_message('Before inserting record in PA_PWP_AP_INV_HDR'||
'[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
'[l_stage : '||l_stage||'] '||
'[L_PA_PWP_AP_HDR_ID : '||L_PA_PWP_AP_HDR_ID||'] ',
'Process_SuppInv_Dtls1');
Insert Into PA_PWP_AP_INV_HDR(PA_PWP_AP_HDR_ID
,PROJECT_ID
,INVOICE_ID
,INVOICE_NUM
,vendOR_ID
,SUPPLIER_NUM
,SUPPLIER_NAME
,SUPPLIER_SITE_CODE
,INVOICE_Date
,INVOICE_AMOUNT
,INVOICE_Currency
,HOLD_REASON
,PWP_HOLD_FLAG
,DLV_HOLD_FLAG
,HOLD_FLAG
,PAYMENT_STATUS
,LINKED_DRAFT_INVOICE_NUM
,LINKED_DRFAT_INV_TYPE
,hold_applied_yn
,description
,exchange_rate
,exchange_date
,exchange_rate_type
,gl_date
,invoice_type
,earliest_pay_due_date
,prepaid_amount
) Values(
l_PA_PWP_AP_HDR_ID
,INVREC.PROJECT_ID
,INVREC.INVOICE_ID
,INVREC.INVOICE_NUM
,INVREC.vendOR_ID
,INVREC.Supplier_Num
,INVREC.SUPPLIER_NAME
,INVREC.SUPPLIER_SITE
,INVREC.INVOICE_Date
,INVREC.INVOICE_AMOUNT
,INVREC.INVOICE_Currency
,l_hold_reason
,l_inv_pwp_hold
,l_inv_dlv_hold
,l_inv_hold
,l_inv_paid
,l_draft_inv_number
,l_draft_inv_link_type
,l_hold_applied_yn
, inv_description
,inv_ex_rate
,inv_ex_date
,inv_ex_rtype1
,inv_gl_date
,inv_type
,inv_due_date
,inv_prepay_amt
);
log_message('Before opening the loop for inserting record in PA_PWP_AP_INV_DTL '||
'[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
'[l_stage : '||l_stage||'] ',
'Process_SuppInv_Dtls1');
select sum(apd.amount) ProjInvAmount into l_invrec_amount
from ap_invoice_dIstributions_all apd,
ap_invoice_dIstributions_all rc_tax,
po_dIstributions_all pod
where
apd.project_Id = INVDTL.project_Id
and rc_tax.line_type_lookup_code(+) = 'REC_TAX'
and rc_tax.tax_recoverable_flag(+) = 'Y'
and rc_tax.charge_applicable_to_dist_id(+)=apd.invoice_distribution_id
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and rc_tax.posted_flag(+) = 'Y'
and apd.invoice_id = INVDTL.invoice_id
and apd.task_id = INVDTL.task_id
and apd.Expenditure_Item_Date = INVDTL.Expenditure_Item_Date
and pod.po_header_id = INVDTL.po_header_id
and rc_tax.invoice_id(+) = INVDTL.invoice_id
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and nvl(rc_tax.reversal_flag(+),'N') = 'N';
select sum(apd.amount) ProjInvAmount into l_invrec_amount
from ap_invoice_dIstributions_all apd,
ap_invoice_dIstributions_all rc_tax,
po_dIstributions_all pod
where
apd.project_Id = INVDTL.project_Id
and rc_tax.line_type_lookup_code(+) = 'REC_TAX'
and rc_tax.tax_recoverable_flag(+) = 'Y'
and
rc_tax.charge_applicable_to_dist_id(+)=apd.invoice_distribution_id
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and rc_tax.posted_flag(+) = 'Y'
and apd.invoice_id = INVDTL.invoice_id
and apd.task_id = INVDTL.task_id
and apd.Expenditure_Item_Date = INVDTL.Expenditure_Item_Date
and rc_tax.invoice_id(+) = INVDTL.invoice_id
and apd.po_distribution_id is null
and apd.posted_flag = 'Y'
and pod.po_dIstribution_id(+) = apd.po_distribution_id
and nvl(rc_tax.reversal_flag(+),'N') = 'N';
log_message('Before inserting record in PA_PWP_AP_INV_DTL '||
'[INVREC.INVOICE_ID : '||INVREC.INVOICE_ID||'] '||
'[l_stage : '||l_stage||'] ',
'Process_SuppInv_Dtls1');
Insert into PA_PWP_AP_INV_DTL(PA_PWP_AP_HDR_ID
,INVOICE_ID
,PROJECT_ID
,TASK_ID
,Expenditure_Item_Date
,PO_HEADER_ID
,INVOICE_Currency
,PROJINV_TOT_AMOUNT
,AMOUNT_PAID
,AMOUNT_UNPAID
,RETAINED_AMOUNT
,DISCOUNT_AMOUNT
,ProjFunc_Currency_CODE
,ProjFunc_INVOICE_AMOUNT
,ProjFunc_INV_PAID_AMOUNT
,ProjFunc_INV_UNPAID_AMOUNT
,ProjFunc_RETAINED_AMOUNT
,ProjFunc_DISCOUNT_AMOUNT
,Proj_Currency_CODE
,Proj_INVOICE_AMOUNT
,Proj_INV_PAID_AMOUNT
,Proj_INV_UNPAID_AMOUNT
,Proj_RETAINED_AMOUNT
,Proj_DISCOUNT_AMOUNT
,ACCT_Currency_CODE
,ACCT_INVOICE_AMOUNT
,ACCT_INV_PAID_AMOUNT
,ACCT_INV_UNPAID_AMOUNT
,ACCT_RETAINED_AMOUNT
,ACCT_DISCOUNT_AMOUNT
,PROJFUNC_CUR_PER_INV_PAID
,PROJ_CUR_PER_INV_PAID
,PROJFUNC_RTAX_AMOUNT
,PROJ_RTAX_AMOUNT
,ACCT_RTAX_AMOUNT
,RTAX_AMOUNT)
VALUES (
l_PA_PWP_AP_HDR_ID
,INVREC.INVOICE_ID
,INVREC.PROJECT_ID
,INVDTL.TASK_ID
,INVDTL.Expenditure_Item_Date
,INVDTL.PO_HEADER_ID
,INVREC.INVOICE_Currency
,INVOICE_AMOUNT
,AMOUNT_PAID
,AMOUNT_UNPAID
,Outstanding_Retainage -- 8310848
,DISCOUNT_AMOUNT
,ProjFunc_Currency
,ProjFunc_INVOICE_AMT
,ProjFunc_AMT_PAID
,ProjFunc_AMT_UNPAID
,ProjFunc_retainage
,ProjFunc_DISCOUNT_AMT
,Proj_Currency
,Proj_INVOICE_AMT
,Proj_AMT_PAID
,Proj_AMT_UNPAID
,Proj_Retainage
,Proj_DISCOUNT_AMT
,PA_CURR_CODE
,ACCT_INVOICE_AMT
,ACCT_AMT_PAID
,ACCT_AMT_UNPAID
,Acct_retainage
,ACCT_DISCOUNT_AMT
,ProjFunc_Cur_Per_AMT_PAID
,Proj_Cur_Per_AMT_PAID
,ProjFunc_Rc_Tax
,Proj_Rc_Tax
,Acct_Rc_Tax
,Rc_Tax);
Update PA_PWP_AP_INV_HDR
Set po_number = l_po_number
Where pa_pwp_ap_hdr_id = l_pa_pwp_ap_hdr_id;
Select hold_lookup_code, hold_reason, 'PWP/DLV' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null
UNION ALL
Select hold_lookup_code, hold_reason, 'OTH' HoldType From ap_holds_all
Where invoice_id= P_Invoice_Id
And hold_lookup_code Not In ('Pay When Paid','PO Deliverable')
and RELEASE_REASON is null;
AP_HOLDS_PKG.insert_single_hold (X_invoice_id=>P_Inv_Tbl(i)
,X_hold_lookup_code=>'Project Hold' -- bug 9525493
,X_hold_reason=>'Project Managers Hold'
,X_held_by=>FND_GLOBAL.User_Id);
Update PA_PWP_AP_INV_HDR Set HOLD_REASON = l_hold_reason1,
PWP_HOLD_FLAG = l_inv_pwp_hold,
DLV_HOLD_FLAG = l_inv_dlv_hold,
HOLD_FLAG = l_inv_hold,
HOLD_APPLIED_YN = l_hold_applied_yn
Where Invoice_Id = p_inv_tbl(i)
And RELHOLD_REJ_REASON Is Null;