[Home] [Help]
51: 'OFFINVOICE', qpr_sr_util.get_all_oad_desc, qpr_sr_util.get_null_desc)
52: erosion_desc,
53: decode(sum(l.ordered_qty), 0 ,0, sum(erosion_amount) * count(erosion_type)/sum(l.ordered_qty)),
54: sum(erosion_amount)
55: from qpr_pn_pr_details a, qpr_pn_lines l
56: where a.response_header_id = p_response_hdr_id
57: and a.pn_line_id = l.pn_line_id
58: group by a.erosion_type, a.pn_line_id;
59:
60: cursor c_hdr_adj is
61: select erosion_type,
62: erosion_name, erosion_desc, null,
63: sum(erosion_amount)
64: from qpr_pn_pr_details
65: where response_header_id = p_response_hdr_id
66: group by erosion_type,erosion_name, erosion_desc;
67:
68: cursor c_model_adj is
69: select
70: erosion_type, erosion_name, erosion_desc,
71: decode(nvl(p_mdl_qty,0), 0, 0, sum(a.erosion_amount)/p_mdl_qty) as unit_adj,
72: decode(nvl(p_mdl_qty,0), 0, 0, sum(a.erosion_amount)) as erosion_amount
73: from qpr_pn_pr_details a, qpr_pn_lines l
74: where l.parent_pn_line_id= p_top_mdl_src_line_id
75: and l.response_header_id = p_response_hdr_id
76: and a.pn_line_id = l.pn_line_id
77: group by erosion_type, erosion_name, erosion_desc;
95: fetch c_tot_adj bulk collect into t_line_id, t_er_typ, t_er_name,
96: t_er_desc,t_er_val, t_tot_er limit l_rows;
97: exit when t_line_id.count = 0;
98: forall i in t_line_id.first..t_line_id.last
99: insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
100: RESPONSE_HEADER_ID,
101: PN_LINE_ID,
102: EROSION_TYPE,
103: EROSION_NAME,
108: CREATED_BY,
109: LAST_UPDATE_DATE,
110: LAST_UPDATED_BY,
111: LAST_UPDATE_LOGIN)
112: values(qpr_pn_pr_details_s.nextval,
113: p_response_hdr_id,
114: t_line_id(i),
115: t_er_typ(i),
116: t_er_name(i),
150: close c_adj;
151: end if;
152:
153: forall i in t_er_name.first..t_er_name.last
154: insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
155: RESPONSE_HEADER_ID,
156: PN_LINE_ID,
157: EROSION_TYPE,
158: EROSION_NAME,
163: CREATED_BY,
164: LAST_UPDATE_DATE,
165: LAST_UPDATED_BY,
166: LAST_UPDATE_LOGIN)
167: values(qpr_pn_pr_details_s.nextval,
168: p_response_hdr_id,
169: p_pn_line_id,
170: t_er_typ(i),
171: t_er_name(i),
176: FND_GLOBAL.USER_ID,
177: SYSDATE,
178: FND_GLOBAL.USER_ID,
179: FND_GLOBAL.CONC_LOGIN_ID);
180: log_debug('Inserted ' || sql%rowcount || ' adjustment rows in qpr_pn_pr_details');
181:
182: t_er_typ.delete;
183: t_er_name.delete;
184: t_er_desc.delete;
303: else
304: begin
305: select nvl(sum(erosion_amount),0), nvl(sum(erosion_per_unit), 0)
306: into l_tot_erosion,l_unit_er
307: from qpr_pn_pr_details
308: where pn_line_id = p_pn_line_id
309: and erosion_type = r_pr_types.erosion_type;
310: end;
311:
570:
571: begin
572: select nvl(sum(p.erosion_per_unit),0)
573: into l_cost
574: from qpr_pn_pr_details p
575: where p.pn_line_id = p_pn_line_id
576: and p.erosion_type = 'COST';
577:
578: select nvl(min(p.policy_price) , 0) into l_floor_mrg
2001: -- item_type_code, since the model/dummy_parent have same source_ref_line_id
2002: cursor c_pn_pr_det(p_new_resp_hdr_id number) is
2003: select pr.erosion_type, pr.erosion_name, pr.erosion_desc,
2004: pr.erosion_per_unit,pr.erosion_amount, nl.pn_line_id
2005: from qpr_pn_pr_details pr, qpr_pn_lines ol, qpr_pn_lines nl
2006: where
2007: pr.response_header_id = p_response_hdr_id
2008: and pr.response_header_id = ol.response_header_id(+)
2009: and pr.pn_line_id = ol.pn_line_id(+)
2239: t_unit_val, t_amnt, t_line_id
2240: limit l_rows;
2241: exit when t_line_id.count = 0;
2242: forall i in t_line_id.first..t_line_id.last
2243: insert into qpr_pn_pr_details(PN_PR_DETAIL_ID,
2244: RESPONSE_HEADER_ID,
2245: PN_LINE_ID,
2246: EROSION_TYPE,
2247: EROSION_NAME,
2252: CREATED_BY,
2253: LAST_UPDATE_DATE,
2254: LAST_UPDATED_BY,
2255: LAST_UPDATE_LOGIN)
2256: values(qpr_pn_pr_details_s.nextval,
2257: l_response_id,
2258: t_line_id(i),
2259: t_er_type(i),
2260: t_er_name(i),