[Home] [Help]
7: +===========================================================================+
8: | |
9: | File Name : BOMDELEB.pls |
10: | DESCRIPTION : This file is a packaged body for deleting
11: | records from bom_explosions table where the rexplode flag
12: | is set to 1
13: | Parameters: 1 - top bill sequence id , 2-explosion type
14: | error_code error code
15: | error_msg error message
15: | error_msg error message
16: |History :
17: |23-JUN-03 Sangeetha CREATED
18: +==========================================================================*/
19: PROCEDURE DELETE_BOM_EXPLOSIONS(
20: ERRBUF IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
21: RETCODE IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
22: top_bill_seq_id IN Number ,
23: expl_type IN Varchar2
27: INVALID_ARGUMENT_LIST Exception;
28: Cursor Get_Delete_Rows IS
29: Select top_bill_sequence_id, bill_sequence_id,
30: explosion_type, sort_order
31: From bom_explosions
32: where top_bill_sequence_id = top_bill_seq_id
33: and explosion_type = expl_type
34: and rexplode_flag = 1 ;
35:
48:
49: For Delete_Rows in Get_Delete_Rows
50: Loop
51: Loop
52: Delete from bom_explosions BE
53: Where
54: BE.top_bill_sequence_id = DELETE_ROWS.top_bill_sequence_id
55: And BE.EXPLOSION_TYPE =
56: DELETE_ROWS.explosion_type
61: COMMIT;
62: End Loop;
63: END LOOP ;
64:
65: UPDATE BOM_EXPLOSIONS
66: SET REQUEST_ID = NULL
67: WHERE TOP_BILL_SEQUENCE_ID = top_bill_seq_id
68: AND EXPLOSION_TYPE = expl_type
69: AND SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
74: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
75:
76: EXCEPTION
77: WHEN invalid_argument_list THEN
78: UPDATE BOM_EXPLOSIONS
79: SET REQUEST_ID = NULL
80: WHERE TOP_BILL_SEQUENCE_ID = top_bill_seq_id
81: AND EXPLOSION_TYPE = expl_type
82: AND SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
85: RETCODE := 2;
86: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
87: When Others Then
88: Rollback;
89: UPDATE BOM_EXPLOSIONS
90: SET REQUEST_ID = NULL
91: WHERE TOP_BILL_SEQUENCE_ID = top_bill_seq_id
92: AND EXPLOSION_TYPE = expl_type
93: AND SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;
99: FND_FILE.PUT_LINE(FND_FILE.LOG,'Others : '||SQLCODE || ':'||SQLERRM) ;
100: RETCODE := 2;
101: conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
102:
103: END DELETE_BOM_EXPLOSIONS;
104:
105: PROCEDURE GET_TOP_BILL(Item_Id IN Number,
106: Org_Id IN Number,
107: Alt_Bom_Desg IN VARCHAR2,
110:
111: Cursor get_top(Bill_Seq_Id Number)
112: IS
113: Select top_bill_sequence_id, explosion_type, sort_order
114: from bom_explosions BE
115: where BE.COMP_COMMON_BILL_SEQ_ID = Bill_Seq_Id
116: and BE.rexplode_flag = 1
117: and BE.explosion_type in ('INCLUDED','OPTIONAL','ALL');
118:
118:
119: Cursor bom_expl(top_bill_id Number, expl_type VARCHAR2)
120: IS
121: SELECT Request_id
122: FROM Bom_explosions
123: WHERE top_bill_sequence_id = top_bill_id
124: AND explosion_type = expl_type
125: AND sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode;
126:
131: expl_row_cnt Number := 0;
132:
133: Begin
134:
135: l_delete_bom_expl := fnd_profile.value('BOM:DELETE_BOM_EXPLOSIONS');
136:
137: If l_delete_bom_expl = 1 then
138: SELECT bill_sequence_id
139: INTO Bill_Id
147: expl_row_cnt := 0;
148: Begin
149: SELECT count(*)
150: into expl_row_cnt
151: FROM bom_explosions
152: WHERE top_bill_sequence_id = C1.top_bill_sequence_id
153: AND explosion_type = C1.explosion_type
154: AND sort_order like C1.sort_order||'%'
155: AND sort_order <> C1.sort_order;
166: sub_request => FALSE,
167: argument1 => C1.top_bill_sequence_id,
168: argument2 => C1.explosion_type);
169: If (X_req_id <> 0) then
170: Update bom_explosions
171: set REQUEST_ID = X_req_id
172: where top_bill_sequence_id = C1.top_bill_sequence_id
173: and explosion_type = C1.explosion_type
174: and SORT_ORDER = Bom_Common_Definitions.G_Bom_Init_SortCode;