[Home] [Help]
879: and e.event_type = et.event_type
880: and et.event_type_classification = 'AUTOMATIC')
881: AND exists /* check if the invoice released is related to this automatic event */
882: (select 1
883: from pa_draft_invoice_items dii
884: where dii.project_id = rdl.project_id
885: and dii.draft_invoice_num = P_Draft_Invoice_Num
886: and dii.event_num is not null
887: and dii.event_num = rdl.event_num
998: i.inv_currency_code,
999: sum(ii.inv_amount)
1000: FROM pa_projects p,
1001: pa_draft_invoices i,
1002: pa_draft_invoice_items ii
1003: WHERE p.project_id = i.project_id
1004: AND i.project_id = ii.project_id
1005: AND i.draft_invoice_num = ii.draft_invoice_num
1006: AND i.request_id = P_Request_ID
1031: i.inv_currency_code,
1032: sum(ii.inv_amount)
1033: FROM pa_projects p,
1034: pa_draft_invoices i,
1035: pa_draft_invoice_items ii
1036: WHERE p.project_id = i.project_id
1037: AND i.project_id = ii.project_id
1038: AND i.draft_invoice_num = ii.draft_invoice_num
1039: AND i.request_id = P_Request_ID
1676:
1677: SELECT ABS(nvl(SUM(nvl(ii.inv_amount,0)),0))
1678: INTO l_tot_credited_amt
1679: FROM pa_draft_invoices i,
1680: pa_draft_invoice_items ii
1681: WHERE i.project_id = p_project_id
1682: AND ii.project_id = i.project_id
1683: AND ii.draft_invoice_num = i.draft_invoice_num
1684: AND i.draft_invoice_num_credited = p_draft_invoice_num
1928: x_msg_count := 0;
1929:
1930: /* Update credit_process_flag, credit_amount to NULL */
1931:
1932: update pa_draft_invoice_items
1933: set credit_process_flag = NULL,
1934: credit_amount = NULL
1935: where project_id = p_project_id
1936: and draft_invoice_num = p_draft_invoice_num;
2014: if p_credit_action_type = 'SUMMARY' then
2015:
2016: /* Update all lines of draft_invoice_items for credit_process_flag = 'Y' */
2017:
2018: update pa_draft_invoice_items
2019: set credit_process_flag = 'Y'
2020: where project_id = p_project_id
2021: and draft_invoice_num = p_draft_invoice_num
2022: /* Added for Payroll Billing ER #11896864 - Start */
2029: if p_credit_action_type = 'GROUP' then
2030:
2031: /* Update specific line of draft_invoice_items for credit_process_flag = 'Y' */
2032:
2033: update pa_draft_invoice_items
2034: set credit_process_flag = 'Y'
2035: where project_id = p_project_id
2036: and draft_invoice_num = p_draft_invoice_num
2037: and line_num = p_draft_invoice_line_num
2042: elsif p_credit_action_type = 'LINE' then
2043:
2044: /* Update specific line of draft_invoice_items for credit_process_flag = 'Y'and credit_amount = p_line_credit_amount */
2045:
2046: update pa_draft_invoice_items
2047: set credit_process_flag = 'Y',
2048: credit_amount = p_line_credit_amount
2049: where project_id = p_project_id
2050: and draft_invoice_num = p_draft_invoice_num
2240: to define the precision of the computed credit amount */
2241:
2242: select sum(dii.inv_amount), max(di.inv_currency_code)
2243: into l_inv_amount, l_inv_currency_code
2244: from pa_draft_invoice_items dii, pa_draft_invoices di
2245: where di.project_id = p_project_id
2246: and di.draft_invoice_num = p_draft_invoice_num
2247: and dii.project_id = di.project_id
2248: and dii.draft_invoice_num = di.draft_invoice_num
2247: and dii.project_id = di.project_id
2248: and dii.draft_invoice_num = di.draft_invoice_num
2249: and nvl(dii.credit_process_flag,'N') = 'Y' ;
2250:
2251: update pa_draft_invoice_items dii
2252: set credit_amount =
2253: pa_currency.round_trans_currency_amt(
2254: (inv_amount * (p_total_credit_amount/l_inv_amount)),
2255: rtrim(l_inv_currency_code))
2361: INTO l_retention_percentage, l_inv_currency_code
2362: FROM pa_draft_invoices
2363: WHERE project_id = p_project_id
2364: AND draft_invoice_num = p_draft_invoice_num
2365: AND EXISTS (select null from pa_draft_invoice_items
2366: WHERE project_id = p_project_id
2367: AND draft_invoice_num = p_draft_invoice_num
2368: AND invoice_line_type = 'RETENTION');
2369:
2375: Since retention amount is always negative, the calculated retention credit amount is multiplied by -1 */
2376:
2377: select sum(nvl(credit_amount,0))
2378: into l_tot_credit_amount
2379: from pa_draft_invoice_items
2380: where project_id = p_project_id
2381: and draft_invoice_num = p_draft_invoice_num;
2382:
2383: l_retn_credit_amount := (-1) * (l_tot_credit_amount * l_retention_percentage)/100;
2383: l_retn_credit_amount := (-1) * (l_tot_credit_amount * l_retention_percentage)/100;
2384:
2385: /* In old model there will be only one retention line for an invoice. So credit amount is stamped on that line */
2386:
2387: update pa_draft_invoice_items
2388: set credit_amount =
2389: pa_currency.round_trans_currency_amt(l_retn_credit_amount, rtrim(l_inv_currency_code))
2390: where project_id = p_project_id
2391: and draft_invoice_num = p_draft_invoice_num
2422: nvl(dii.retention_rule_id,-1) retention_rule_id,
2423: nvl(dii.retained_amount,0) retained_amount,
2424: dii.amount amount,
2425: dii.credit_amount credit_amount
2426: from pa_draft_invoice_items dii
2427: where project_id = p_project_id
2428: AND draft_invoice_num = p_draft_invoice_num
2429: AND invoice_line_type <> 'RETENTION'
2430: AND nvl(dii.credit_amount, 0) <> 0
2580:
2581: /* Since retention credit amount in the original invoice should be negative and precision should be based
2582: on currency code we multiply by -1 and round the amount based on inv_currency_code
2583: */
2584: update pa_draft_invoice_items
2585: set credit_amount =
2586: pa_currency.round_trans_currency_amt(credit_amount, rtrim(l_inv_currency_code)) * -1
2587: where project_id = p_project_id
2588: and draft_invoice_num = p_draft_invoice_num
2690:
2691: /* Since one line of draft invoice item may have multiple lines in RDL/ERDL this procedure may be called more than
2692: once for the same line. So credit amount is acutally summed up */
2693:
2694: update pa_draft_invoice_items
2695: set credit_amount = nvl(credit_amount ,0) + l_retn_credit_amount
2696: where project_id = p_project_id
2697: and draft_invoice_num = p_draft_invoice_num
2698: and line_num = p_retention_line_num;