[Home] [Help]
37: /*The below cursor will select unassigned time forecast_items whose item date <=purge till date. */
38:
39: CURSOR Cur_forecast_items IS
40: SELECT forecast_item_id ,project_id --Added for bug 5870223
41: FROM pa_forecast_items
42: WHERE forecast_item_type='U'
43: AND item_date <= fnd_date.canonical_to_date(p_txn_to_date) /* Bug#2510609 */
44: ORDER BY project_id; -- Added for bug 5870223
45:
150: -- Start of comments
151: -- API name : DELETE_FI
152: -- Type : Public
153: -- Pre-reqs : None
154: -- Function : Archive/purge records for pa_forecast_items, pa_forecast_items_details and pa_fi_amount_details table.
155: -- Parameters :
156: -- records need to be archived
157: -- p_forecast_item_id_id_tab -> forecast items tab
158: -- End of comments
164: x_err_stack in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
165: x_err_stage in OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
166: x_err_code in OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
167:
168: l_forecast_item_id Pa_forecast_items.forecast_item_id%TYPE;
169: I PLS_INTEGER;
170: l_nos_fi_inserted NUMBER ;
171: l_nos_fid_inserted NUMBER ;
172: l_nos_fi_deleted NUMBER ;
226: --End for bug 5870223
227:
228: /*Commented for bug 5870223
229: SELECT project_id into l_project_id
230: from pa_forecast_items
231: where forecast_item_id=l_forecast_item_id;*/
232:
233: l_project_id := p_project_id_tab(I); --Added for bug 5870223
234:
237: IF l_call_commit = 'Y' THEN -- Bug 5870223
238:
239: IF p_archive_flag='Y' THEN
240:
241: /* arpr_log('Inserting Records into pa_forecast_items_AR table ') ; */
242: x_err_stage := 'Inserting Records into pa_forecast_items_AR table for forecast item '||to_char(l_forecast_item_id) ;
243:
244: FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
245: INSERT INTO pa_frcst_items_AR
238:
239: IF p_archive_flag='Y' THEN
240:
241: /* arpr_log('Inserting Records into pa_forecast_items_AR table ') ; */
242: x_err_stage := 'Inserting Records into pa_forecast_items_AR table for forecast item '||to_char(l_forecast_item_id) ;
243:
244: FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
245: INSERT INTO pa_frcst_items_AR
246: (PURGE_BATCH_ID,
399: Over_Prov_Conf_Qty,
400: Confirmed_Qty,
401: Provisional_Qty,
402: Job_Id
403: FROM pa_forecast_items
404: WHERE forecast_item_id = l_forecast_item_id_tab(K);--l_forecast_item_id; 5870223
405:
406: /*Increase the value of l_nos_fi_inserted to indicate number of records inserted in forecast_items table.
407: The value will increase for each loop(forecast item id*/
664: l_nos_fi_amt_inserted := SQL%ROWCOUNT; /* Bug#2510609 */
665:
666: END IF;
667:
668: /*To keep the count of no os records deleted from pa_forecast_items and pa_forecast_item_details, manipulate the
669: count of l_nos_of fi_deleted and l_nos_fis_deleted. */
670:
671: /* arpr_log('Deleting Records from pa_fi_amount_details table ') ; */
672: x_err_stage := 'Deleting Records from pa_fi_amount_details table for id '||to_char(l_forecast_item_id) ;
687: WHERE forecast_item_id =l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
688:
689: l_nos_fid_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
690:
691: /* arpr_log('Deleting Records from pa_forecast_items table ') ; */
692: x_err_stage := 'Deleting Records from pa_forecast_items table for id '||to_char(l_forecast_item_id) ;
693:
694: FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
695:
688:
689: l_nos_fid_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
690:
691: /* arpr_log('Deleting Records from pa_forecast_items table ') ; */
692: x_err_stage := 'Deleting Records from pa_forecast_items table for id '||to_char(l_forecast_item_id) ;
693:
694: FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
695:
696: DELETE PA_FORECAST_ITEMS
692: x_err_stage := 'Deleting Records from pa_forecast_items table for id '||to_char(l_forecast_item_id) ;
693:
694: FORALL K IN l_forecast_item_id_tab.FIRST..l_forecast_item_id_tab.LAST -- Bug 5870223
695:
696: DELETE PA_FORECAST_ITEMS
697: WHERE forecast_item_id=l_forecast_item_id_tab(K);--l_forecast_item_id; Bug 5870223
698:
699: l_nos_fi_deleted :=SQL%ROWCOUNT; /* Bug#2510609 */
700:
700:
701: -- END LOOP; /* Bug#2510609 */
702:
703: /*After "deleting" or "deleting and inserting" a set of records the transaction is commited. This also creates a record in the Pa_Purge_Project_details, which will show the no. of records that are purged from each table.
704: The procedure is called once for pa_forecast_items and once for pa_forecast_item_details */
705:
706:
707: pa_purge.CommitProcess (p_purge_batch_id,
708: l_project_id,
705:
706:
707: pa_purge.CommitProcess (p_purge_batch_id,
708: l_project_id,
709: 'PA_FORECAST_ITEMS',
710: l_nos_fi_inserted,
711: l_nos_fi_deleted,
712: x_err_code,
713: x_err_stack,