[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
1622:
1623: SELECT ABS(nvl(SUM(nvl(ii.inv_amount,0)),0))
1624: INTO l_tot_credited_amt
1625: FROM pa_draft_invoices i,
1626: pa_draft_invoice_items ii
1627: WHERE i.project_id = p_project_id
1628: AND ii.project_id = i.project_id
1629: AND ii.draft_invoice_num = i.draft_invoice_num
1630: AND i.draft_invoice_num_credited = p_draft_invoice_num
1809: x_msg_count := 0;
1810:
1811: /* Update credit_process_flag, credit_amount to NULL */
1812:
1813: update pa_draft_invoice_items
1814: set credit_process_flag = NULL,
1815: credit_amount = NULL
1816: where project_id = p_project_id
1817: and draft_invoice_num = p_draft_invoice_num;
1895: if p_credit_action_type = 'SUMMARY' then
1896:
1897: /* Update all lines of draft_invoice_items for credit_process_flag = 'Y' */
1898:
1899: update pa_draft_invoice_items
1900: set credit_process_flag = 'Y'
1901: where project_id = p_project_id
1902: and draft_invoice_num = p_draft_invoice_num;
1903:
1907: if p_credit_action_type = 'GROUP' then
1908:
1909: /* Update specific line of draft_invoice_items for credit_process_flag = 'Y' */
1910:
1911: update pa_draft_invoice_items
1912: set credit_process_flag = 'Y'
1913: where project_id = p_project_id
1914: and draft_invoice_num = p_draft_invoice_num
1915: and line_num = p_draft_invoice_line_num;
1917: elsif p_credit_action_type = 'LINE' then
1918:
1919: /* Update specific line of draft_invoice_items for credit_process_flag = 'Y'and credit_amount = p_line_credit_amount */
1920:
1921: update pa_draft_invoice_items
1922: set credit_process_flag = 'Y',
1923: credit_amount = p_line_credit_amount
1924: where project_id = p_project_id
1925: and draft_invoice_num = p_draft_invoice_num
2112: to define the precision of the computed credit amount */
2113:
2114: select sum(dii.inv_amount), max(di.inv_currency_code)
2115: into l_inv_amount, l_inv_currency_code
2116: from pa_draft_invoice_items dii, pa_draft_invoices di
2117: where di.project_id = p_project_id
2118: and di.draft_invoice_num = p_draft_invoice_num
2119: and dii.project_id = di.project_id
2120: and dii.draft_invoice_num = di.draft_invoice_num
2119: and dii.project_id = di.project_id
2120: and dii.draft_invoice_num = di.draft_invoice_num
2121: and nvl(dii.credit_process_flag,'N') = 'Y' ;
2122:
2123: update pa_draft_invoice_items dii
2124: set credit_amount =
2125: pa_currency.round_trans_currency_amt(
2126: (inv_amount * (p_total_credit_amount/l_inv_amount)),
2127: rtrim(l_inv_currency_code))
2233: INTO l_retention_percentage, l_inv_currency_code
2234: FROM pa_draft_invoices
2235: WHERE project_id = p_project_id
2236: AND draft_invoice_num = p_draft_invoice_num
2237: AND EXISTS (select null from pa_draft_invoice_items
2238: WHERE project_id = p_project_id
2239: AND draft_invoice_num = p_draft_invoice_num
2240: AND invoice_line_type = 'RETENTION');
2241:
2247: Since retention amount is always negative, the calculated retention credit amount is multiplied by -1 */
2248:
2249: select sum(nvl(credit_amount,0))
2250: into l_tot_credit_amount
2251: from pa_draft_invoice_items
2252: where project_id = p_project_id
2253: and draft_invoice_num = p_draft_invoice_num;
2254:
2255: l_retn_credit_amount := (-1) * (l_tot_credit_amount * l_retention_percentage)/100;
2255: l_retn_credit_amount := (-1) * (l_tot_credit_amount * l_retention_percentage)/100;
2256:
2257: /* In old model there will be only one retention line for an invoice. So credit amount is stamped on that line */
2258:
2259: update pa_draft_invoice_items
2260: set credit_amount =
2261: pa_currency.round_trans_currency_amt(l_retn_credit_amount, rtrim(l_inv_currency_code))
2262: where project_id = p_project_id
2263: and draft_invoice_num = p_draft_invoice_num
2294: nvl(dii.retention_rule_id,-1) retention_rule_id,
2295: nvl(dii.retained_amount,0) retained_amount,
2296: dii.amount amount,
2297: dii.credit_amount credit_amount
2298: from pa_draft_invoice_items dii
2299: where project_id = p_project_id
2300: AND draft_invoice_num = p_draft_invoice_num
2301: AND invoice_line_type <> 'RETENTION'
2302: AND nvl(dii.credit_amount, 0) <> 0
2452:
2453: /* Since retention credit amount in the original invoice should be negative and precision should be based
2454: on currency code we multiply by -1 and round the amount based on inv_currency_code
2455: */
2456: update pa_draft_invoice_items
2457: set credit_amount =
2458: pa_currency.round_trans_currency_amt(credit_amount, rtrim(l_inv_currency_code)) * -1
2459: where project_id = p_project_id
2460: and draft_invoice_num = p_draft_invoice_num
2562:
2563: /* Since one line of draft invoice item may have multiple lines in RDL/ERDL this procedure may be called more than
2564: once for the same line. So credit amount is acutally summed up */
2565:
2566: update pa_draft_invoice_items
2567: set credit_amount = nvl(credit_amount ,0) + l_retn_credit_amount
2568: where project_id = p_project_id
2569: and draft_invoice_num = p_draft_invoice_num
2570: and line_num = p_retention_line_num;