[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select to_char(draft_invoice_num) draft_inv_num
FROM pa_draft_invoices_all
WHERE project_id = P_Project_ID
AND invoice_set_id = (select invoice_set_id from
pa_draft_invoices_all
where draft_invoice_num = p_draft_inv_num
and project_id = p_project_id)
and nvl(customer_bill_split,0) not in (0,100);
SELECT nvl(multi_currency_billing_flag, 'N'),
nvl(org_id, -99)
INTO x_mcb_flag,
x_org_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT meaning
INTO x_yes_m
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = 'Y';
SELECT meaning
INTO x_no_m
FROM fnd_lookups
WHERE lookup_type = 'YES_NO'
AND lookup_code = 'N';
SELECT employee_id
INTO x_person_id
FROM fnd_user
WHERE user_id = x_user_id;
SELECT full_name
INTO x_employee_name
FROM pa_employees
WHERE person_id = x_person_id;
SELECT count(*)
INTO l_warning_count
FROM pa_distribution_warnings
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_inv_num;
SELECT meaning
INTO x_na_m
FROM pa_lookups
WHERE lookup_type = 'PA_BILL_WRKBNCH_NA'
AND lookup_code = 'N_A';
SELECT count(*)
INTO l_dummy
FROM PA_Draft_Invoices_ALL i
WHERE i.invoice_set_id = (select invoice_set_id from pa_draft_invoices_all
where project_id = p_project_id
and draft_invoice_num = p_draft_inv_num)
AND i.customer_bill_split not in (0, 100)
AND i.approved_date is null
and i.project_id = p_project_id;
SELECT approved_date
INTO l_approved_date
FROM pa_draft_invoices_all
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
Update Approve Information to the invoice header table
------------------------------------------------------- */
IF (l_approved_date is null ) THEN
UPDATE pa_draft_invoices_all
SET last_update_date = sysdate,
Last_updated_by = p_user_id,
last_update_login = p_login_id,
approved_date = TRUNC(sysdate),
approved_by_person_id = p_person_id
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num ;
SELECT et.event_type_classification
INTO l_event_type
FROM pa_event_types et, pa_events ev
WHERE et.event_type = ev.event_type
AND ev.project_id = p_project_id
AND ev.event_num = p_event_num
AND nvl(ev.task_id,0) = nvl(p_event_task_id,0);
SELECT i.customer_id, i.generation_error_flag,
i.approved_date , p.project_status_code
FROM pa_draft_invoices_all i, /* changed the refrence to base table instead of pa_draft_invoices_all_v for bug # 4666256 */
pa_projects_all p /* added the refrence to pa_projects_all for project_status_code */
WHERE i.project_id = P_Project_ID
AND p.project_id = i.project_id
AND i.draft_invoice_num = P_Draft_Invoice_Num;
SELECT 1
FROM RA_CUSTOMERS
WHERE customer_id = l_customer_id
AND NVL(status, 'A') <> 'A'
AND customer_prospect_code = 'CUSTOMER'; */
SELECT 1
FROM HZ_CUST_ACCOUNTS
WHERE cust_account_id = l_customer_id
AND NVL(status, 'A') <> 'A';
SELECT count(*)
INTO l_dummy
FROM PA_Draft_Invoices_ALL i
WHERE i.invoice_set_id = P_Invoice_Set_ID
AND i.customer_bill_split not in (0, 100)
AND i.approved_date is null;
SELECT project_id, draft_invoice_num, customer_id, approved_date
FROM pa_draft_invoices_all --For Bug 3961053
-- FROM pa_draft_invoices_all_v --For Bug 3961053
WHERE project_id = P_Project_ID
AND invoice_set_id = P_invoice_set_id
and nvl(customer_bill_split,0) not in (0,100);
SELECT count(*) INTO l_inactive_customer FROM ra_customers
WHERE customer_id = inv_rec.customer_id
AND NVL(status,'A') <> 'A'
AND customer_prospect_code = 'CUSTOMER'; */
SELECT count(*) INTO l_inactive_customer FROM hz_cust_accounts
WHERE cust_account_id = inv_rec.customer_id
AND NVL(status,'A') <> 'A';
select draft_invoice_num
FROM pa_draft_invoices_all
WHERE project_id = P_Project_ID
AND invoice_set_id = p_invoice_set_id
and nvl(customer_bill_split,0) not in (0,100);
SELECT count(*)
INTO l_approved_count
FROM pa_draft_invoices_all
WHERE project_id = p_project_id
AND invoice_set_id = p_invoice_set_id
AND approved_date is not null
AND nvl(customer_bill_split,0) not in (0,100);
Update Approve Information to the invoice header table
------------------------------------------------------- */
IF (l_approved_count = 0 ) THEN
for unappr_rec in unappr_cur loop
if l_app_draft_num is not null then
l_app_draft_num := l_app_draft_num ||',';
UPDATE pa_draft_invoices_all
SET last_update_date = sysdate,
Last_updated_by = p_user_id,
last_update_login = p_login_id,
approved_date = TRUNC(sysdate),
approved_by_person_id = p_person_id
WHERE project_id = p_project_id
AND draft_invoice_num = unappr_rec.draft_invoice_num;