Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL error (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit)
| PL/SQL error [message #653525] |
Sat, 09 July 2016 13:51  |
jcasas
Messages: 5
Registered: November 2006
Location: MEXICO
|
Junior Member |
|
|
I'm facing this error message PL/SQL: ORA-00923: FROM keyword not found where expected on my query, I did review and could not find what is happening.
This is my query
select JOBTITLE,promotion_id,
nombre ,DATE_PASSED,
case when instr(cursos,id_curso) > 0 then 1 else 0 end aprobado from (
select distinct a.id_motores_oem , a.motor, c.sp_code from ZMKT7352_MOTORES_OEM a
inner join ZMKT7352_OEM b on a.oem = to_char(b.id_oem)
inner join ZMKT7352_DR_DEALERS_EBU c on b.id_oem = c.MEXICO_EQUIVALENT_OEM
inner join ZMKT7352_MOTORES d
on d.id_motores = a.id_motores_oem
full outer join ZMKT7352_REL_PERFIL_MOTOR e
on e.id_motor = a.id_motores_oem
and c.sp_code = e.sp_code
where c.sp_code = p_code and e.id_motor is not null
) a inner join ZMKT7352_REL_MOTOR_HEADER f
on f.ID_MOTOR = a.id_motores_oem
inner join ZMKT7352_REL_MOTOR_CURSOS g
on f.id_header = g.id_header
full outer join
(select promotion_id, DATE_PASSED, FIRST_NAME||' ' ||LAST_NAME as nombre , LISTAGG(PROGRAM_ID, ',') WITHIN GROUP (ORDER BY PROGRAM_ID) as cursos, sp_code,JOBTITLE from ZMKT7352_PROMOTION
where sp_code = p_code
group by FIRST_NAME,LAST_NAME,sp_code,JOBTITLE,DATE_PASSED,promotion_id) h
on h.sp_code = a.sp_code
where PUESTO = 1
order by motor )
)group by promotion_id, JOBTITLE, nombre, DATE_PASSED
having sum(aprobado) =1 or JOBTITLE in( 'Warranty')
order by nombre;
DB Version
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
|
|
|
|
| Re: PL/SQL error [message #653526 is a reply to message #653525] |
Sat, 09 July 2016 14:10  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read
which line is reported as the problem?
SELECT jobtitle,
promotion_id,
nombre ,
date_passed,
CASE
WHEN Instr(cursos,id_curso) > 0 THEN 1
ELSE 0
END aprobado
FROM (
SELECT DISTINCT a.id_motores_oem ,
a.motor,
c.sp_code
FROM zmkt7352_motores_oem a
inner join zmkt7352_oem b
ON a.oem = To_char(b.id_oem)
inner join zmkt7352_dr_dealers_ebu c
ON b.id_oem = c.mexico_equivalent_oem
inner join zmkt7352_motores d
ON d.id_motores = a.id_motores_oem
full outer join zmkt7352_rel_perfil_motor e
ON e.id_motor = a.id_motores_oem
AND c.sp_code = e.sp_code
WHERE c.sp_code = p_code
AND e.id_motor IS NOT NULL ) a
inner join zmkt7352_rel_motor_header f
ON f.id_motor = a.id_motores_oem
inner join zmkt7352_rel_motor_cursos g
ON f.id_header = g.id_header
full outer join
(
SELECT promotion_id,
date_passed,
first_name
||' '
||last_name AS nombre ,
Listagg(program_id, ',') within GROUP (ORDER BY program_id) AS cursos,
sp_code,
jobtitle
FROM zmkt7352_promotion
WHERE sp_code = p_code
GROUP BY first_name,
last_name,
sp_code,
jobtitle,
date_passed,
promotion_id) h
ON h.sp_code = a.sp_code
WHERE puesto = 1
ORDER BY motor ) )GROUP BY promotion_id, jobtitle, nombre, date_passed HAVING SUM(aprobado) =1
OR
jobtitle IN( 'Warranty') ORDER BY nombre;
|
|
|
|
| Re: PL/SQL error [message #653527 is a reply to message #653525] |
Sat, 09 July 2016 14:13 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
You can use SQL Formatter to help you.
SQL> SELECT jobtitle,
2 promotion_id,
3 nombre ,
4 date_passed,
5 CASE
6 WHEN Instr(cursos,id_curso) > 0 THEN 1
7 ELSE 0
8 END aprobado
9 FROM (
10 SELECT DISTINCT a.id_motores_oem ,
11 a.motor,
12 c.sp_code
13 FROM zmkt7352_motores_oem a
14 INNER JOIN zmkt7352_oem b
15 ON a.oem = To_char(b.id_oem)
16 INNER JOIN zmkt7352_dr_dealers_ebu c
17 ON b.id_oem = c.mexico_equivalent_oem
18 INNER JOIN zmkt7352_motores d
19 ON d.id_motores = a.id_motores_oem
20 FULL OUTER JOIN zmkt7352_rel_perfil_motor e
21 ON e.id_motor = a.id_motores_oem
22 AND c.sp_code = e.sp_code
23 WHERE c.sp_code = p_code
24 AND e.id_motor IS NOT NULL ) a
25 INNER JOIN zmkt7352_rel_motor_header f
26 ON f.id_motor = a.id_motores_oem
27 INNER JOIN zmkt7352_rel_motor_cursos g
28 ON f.id_header = g.id_header
29 FULL OUTER JOIN
30 (
31 SELECT promotion_id,
32 date_passed,
33 first_name
34 ||' '
35 ||last_name AS nombre ,
36 Listagg(program_id, ',') within GROUP (ORDER BY program_id) AS cursos,
37 sp_code,
38 jobtitle
39 FROM zmkt7352_promotion
40 WHERE sp_code = p_code
41 GROUP BY first_name,
42 last_name,
43 sp_code,
44 jobtitle,
45 date_passed,
46 promotion_id) h
47 ON h.sp_code = a.sp_code
48 WHERE puesto = 1
49 ORDER BY motor ) )GROUP BY promotion_id, jobtitle, nombre, date_passed HAVING sum(aprobado) =1
50 OR
51 jobtitle IN( 'Warranty')
52 ORDER BY nombre;
ORDER BY motor ) )GROUP BY promotion_id, jobtitle, nombre, date_passed HAVING sum(aprobado) =1
*
ERROR at line 49:
ORA-00933: SQL command not properly ended
Maybe this is what you wanted to do?
SELECT jobtitle,
promotion_id,
nombre,
date_passed,
CASE
WHEN Instr(cursos, id_curso) > 0 THEN 1
ELSE 0
END aprobado
FROM (SELECT DISTINCT a.id_motores_oem,
a.motor,
c.sp_code
FROM zmkt7352_motores_oem a
inner join zmkt7352_oem b
ON a.oem = To_char(b.id_oem)
inner join zmkt7352_dr_dealers_ebu c
ON b.id_oem = c.mexico_equivalent_oem
inner join zmkt7352_motores d
ON d.id_motores = a.id_motores_oem
full outer join zmkt7352_rel_perfil_motor e
ON e.id_motor = a.id_motores_oem
AND c.sp_code = e.sp_code
WHERE c.sp_code = p_code
AND e.id_motor IS NOT NULL) a
inner join zmkt7352_rel_motor_header f
ON f.id_motor = a.id_motores_oem
inner join zmkt7352_rel_motor_cursos g
ON f.id_header = g.id_header
full outer join (SELECT promotion_id,
date_passed,
first_name
||' '
||last_name AS nombre,
Listagg(program_id, ',')
within GROUP (ORDER BY program_id) AS cursos,
sp_code,
jobtitle
FROM zmkt7352_promotion
WHERE sp_code = p_code
GROUP BY first_name,
last_name,
sp_code,
jobtitle,
date_passed,
promotion_id) h
ON h.sp_code = a.sp_code
WHERE puesto = 1
GROUP BY promotion_id,
jobtitle,
nombre,
date_passed
HAVING SUM(aprobado) = 1
OR jobtitle IN( 'Warranty' )
ORDER BY nombre
/ |
|
|
|
| Re: PL/SQL error [message #653529 is a reply to message #653526] |
Sat, 09 July 2016 14:26 |
jcasas
Messages: 5
Registered: November 2006
Location: MEXICO
|
Junior Member |
|
|
Thanks for your clarification on how to present queries, I did rewrite and here is complete
select promotion_id id_promos,
nombre,
JOBTITLE motor,
DATE_PASSED,
100 posible ,
sum(aprobado)*100 Porcentaje
from (
select distinct promotion_id,
JOBTITLE,
nombre ,
DATE_PASSED,
aprobado
from (
select JOBTITLE,
promotion_id,
nombre ,
DATE_PASSED,
case
when instr(cursos,id_curso) > 0 then 1
else 0
end aprobado
from (
select distinct a.id_motores_oem , --****ERROR IS MARKED IN THIS LINE*****
a.motor,
c.sp_code
from ZMKT7352_MOTORES_OEM a
inner join ZMKT7352_OEM b
on a.oem = to_char(b.id_oem)
inner join ZMKT7352_DR_DEALERS_EBU c
on b.id_oem = c.MEXICO_EQUIVALENT_OEM
inner join ZMKT7352_MOTORES d
on d.id_motores = a.id_motores_oem
full outer join ZMKT7352_REL_PERFIL_MOTOR e
on e.id_motor = a.id_motores_oem
and c.sp_code = e.sp_code
where c.sp_code = p_code
and e.id_motor is not null) a
inner join ZMKT7352_REL_MOTOR_HEADER f
on f.ID_MOTOR = a.id_motores_oem
inner join ZMKT7352_REL_MOTOR_CURSOS g
on f.id_header = g.id_header
full outer join
(
select promotion_id,
DATE_PASSED,
FIRST_NAME
||' '
||LAST_NAME as nombre ,
Listagg(program_id, ',') within GROUP (ORDER BY program_id) AS cursos,
sp_code,
JOBTITLE
from ZMKT7352_PROMOTION
where sp_code = p_code
group by FIRST_NAME,
LAST_NAME,
sp_code,
JOBTITLE,
DATE_PASSED,
promotion_id) h
on h.sp_code = a.sp_code
where PUESTO = 1
order by motor ) ) group by promotion_id, JOBTITLE, nombre, DATE_PASSED having sum(aprobado) =1
or
JOBTITLE in( 'Warranty') order by nombre;
The error does not make me any sense, I have 3 similar error inside my package once I compile it, and other 2 are highlighted in 2 lines that there is no any code there. I'm using TOAD.
Thanks for your help
[Updated on: Sat, 09 July 2016 14:46] Report message to a moderator |
|
|
|
|
|
| Re: PL/SQL error [message #653531 is a reply to message #653530] |
Sat, 09 July 2016 15:28 |
jcasas
Messages: 5
Registered: November 2006
Location: MEXICO
|
Junior Member |
|
|
Thanks for your recommendation here is the complete package body
A lot of code here
CREATE OR replace PACKAGE BODY zmkt."ZMKT7352_INFANT_CARE_PKG"
AS
PROCEDURE Insertar_header(p_matriz_name
zmkt7352_cap_infant_care_head.matriz_name%TYPE,
p_dealer_dr
zmkt7352_cap_infant_care_head.dealer_dr%TYPE,
p_no_revision
zmkt7352_cap_infant_care_head.no_revision%TYPE,
p_anio
zmkt7352_cap_infant_care_head.anio%TYPE,
p_status_des
zmkt7352_cap_infant_care_head.status_des%TYPE,
p_producto_nuevo
zmkt7352_cap_infant_care_head.producto_nuevo%TYPE,
p_tipo_producto
zmkt7352_cap_infant_care_head.tipo_producto%TYPE,
p_oem
zmkt7352_cap_infant_care_head.oem%TYPE,
p_dr
zmkt7352_cap_infant_care_head.dr%TYPE,
p_status_gral
zmkt7352_cap_infant_care_head.status_gral%TYPE,
p_status_fin
zmkt7352_cap_infant_care_head.status_fin%TYPE,
p_rango
zmkt7352_cap_infant_care_head.rango%TYPE,
p_tipo_mer
zmkt7352_cap_infant_care_head.tipo_mer%TYPE,
p_shop_order
zmkt7352_cap_infant_care_head.shop_order%TYPE,
v_id OUT
zmkt7352_cap_infant_care_head.id_matriz%TYPE)
IS
BEGIN
BEGIN
SELECT id_matriz
INTO v_id
FROM zmkt7352_cap_infant_care_head
WHERE matriz_name = p_matriz_name
AND dealer_dr = p_dealer_dr
AND no_revision = p_no_revision
AND anio = p_anio
AND status_des = p_status_des
AND producto_nuevo = p_producto_nuevo
AND oem = p_oem
AND dr = p_dr
AND tipo_mer = p_tipo_mer
AND rango = p_rango
AND delete_ind = 'N';
EXCEPTION
WHEN no_data_found THEN
v_id := 0;
END;
IF v_id = 0 THEN
INSERT INTO zmkt7352_cap_infant_care_head
(id_matriz,
matriz_name,
dealer_dr,
no_revision,
anio,
status_des,
producto_nuevo,
tipo_producto,
oem,
dr,
status_gral,
status_fin,
rango,
tipo_mer,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind,
shop_order)
VALUES ( cap_infant_care_head_seq_id.NEXTVAL,
p_matriz_name,
p_dealer_dr,
p_no_revision,
p_anio,
p_status_des,
p_producto_nuevo,
p_tipo_producto,
p_oem,
p_dr,
p_status_gral,
p_status_fin,
p_rango,
p_tipo_mer,
SYSDATE,
'system',
SYSDATE,
'system',
'N',
p_shop_order )
returning id_matriz INTO v_id;
ELSE
UPDATE zmkt7352_cap_infant_care_head
SET matriz_name = p_matriz_name,
dealer_dr = p_dealer_dr,
no_revision = p_no_revision,
anio = p_anio,
status_des = p_status_des,
producto_nuevo = p_producto_nuevo,
oem = p_oem,
tipo_producto = p_tipo_producto,
dr = p_dr,
tipo_mer = p_tipo_mer,
rango = p_rango,
last_update_date = SYSDATE,
last_update_by = 'system',
shop_order = p_shop_order
WHERE id_matriz = v_id;
END IF;
END insertar_header;
PROCEDURE Insertar_body(p_id_matriz
zmkt7352_cap_infant_care_body.id_matriz%TYPE,
p_no_parte
zmkt7352_cap_infant_care_body.no_parte%TYPE,
p_no_parte_ant
zmkt7352_cap_infant_care_body.no_parte_ant%TYPE,
p_descripcion
zmkt7352_cap_infant_care_body.descripcion%TYPE,
p_ponderacion
zmkt7352_cap_infant_care_body.ponderacion%TYPE,
p_cm zmkt7352_cap_infant_care_body.cm%TYPE,
p_comentarios
zmkt7352_cap_infant_care_body.comentarios%TYPE,
p_url
zmkt7352_cap_infant_care_body.url%TYPE,
p_precio_dlr zmkt7352_cap_infant_care_body.precio_dlr%TYPE,
p_volumen_vta_1 zmkt7352_cap_infant_care_body.volumen_vta_1%TYPE,
p_volumen_vta_2 zmkt7352_cap_infant_care_body.volumen_vta_2%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_cap_infant_care_body
(id_infant_bdy,
id_matriz,
no_parte,
no_parte_ant,
descripcion,
ponderacion,
cm,
comentarios,
url,
precio_dlr,
volumen_vta_1,
volumen_vta_2,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES ( cap_infant_care_body_seq_id.NEXTVAL,
p_id_matriz,
p_no_parte,
p_no_parte_ant,
p_descripcion,
p_ponderacion,
p_cm,
p_comentarios,
p_url,
p_precio_dlr,
p_volumen_vta_1,
p_volumen_vta_2,
SYSDATE,
'system',
SYSDATE,
'system',
'N' );
END insertar_body;
PROCEDURE Obtener_matriz_infant_care(c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
SELECT DISTINCT a.id_matriz AS id,
Decode(a.dealer_dr, 1, 'DR',
2, 'Dealer',
3, 'Planta',
'') AS dealer,
a.matriz_name AS name,
a.last_update_date AS lastUpdate,
b.oem,
shop_order,
a.status_des AS status,
'<a href=javascript:eliminarRegistro('
||a.id_matriz
||
')><img src="img/file_delete.png" alt="Eliminar" style="width:22px; height:22px;"></a>'
eliminar
FROM zmkt7352_cap_infant_care_head a
inner join zmkt7352_oem b
ON a.oem = b.id_oem
WHERE a.tipo_producto = 'Infant Care'
AND a.delete_ind = 'N'
ORDER BY matriz_name;
END obtener_matriz_infant_care;
PROCEDURE Obtener_matriz_teen_care(c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
/*Select distinct a.id_matriz as id, decode(a.dealer_dr,1,'DR',2,'Dealer',3,'Planta','') as dealer, a.matriz_name as name, a.last_update_date
as lastUpdate, b.service_provider_name as DR, a.status_des as status, c.oem from ZMKT7352_OEM c inner join ZMKT7352_CAP_INFANT_CARE_HEAD a on a.oem = c.id_oem
inner join ZMKT7352_DR_DEALERS_EBU b on b.sp_code = a.dr where a.tipo_producto = 'Teen Care' and a.DELETE_IND='N';*/
SELECT DISTINCT a.id_matriz AS id,
Decode(a.dealer_dr, 1, 'DR',
2, 'Dealer',
3, 'Planta',
'') AS dealer,
a.matriz_name AS name,
a.last_update_date AS lastUpdate,
b.service_provider_name AS DR,
a.status_des AS status,
c.oem,
'<a href=javascript:eliminarRegistro('
||a.id_matriz
||
')><img src="img/file_delete.png" alt="Eliminar" style="width:22px; height:22px;"></a>'
eliminar
FROM zmkt7352_cap_infant_care_head a
full outer join zmkt7352_dr_dealers_ebu b
ON b.sp_code = a.dr
inner join zmkt7352_oem c
ON a.oem = c.id_oem
WHERE a.tipo_producto = 'Teen Care'
AND a.delete_ind = 'N'
AND a.id_matriz IS NOT NULL;
END obtener_matriz_teen_care;
PROCEDURE Infant_to_teen_care(v_id
zmkt7352_cap_infant_care_head.id_matriz%TYPE)
IS
BEGIN
UPDATE zmkt7352_cap_infant_care_head
SET tipo_producto = 'Teen Care',
last_update_date = SYSDATE
WHERE id_matriz = v_id;
END infant_to_teen_care;
PROCEDURE Obtener_matriz_teen_care_hmtas(c_matriz OUT SYS_REFCURSOR,
p_id_header IN zmkt7352_rel_motor_herram.id_header%TYPE)
IS
BEGIN
IF ( p_id_header <> '' ) THEN
OPEN c_matriz FOR
SELECT DISTINCT a.id_matriz AS id,
Decode(a.dealer_dr, 1, 'DR',
2, 'Dealer',
3, 'Planta',
'') AS dealer,
a.matriz_name AS name,
a.last_update_date AS lastUpdate,
Decode(id_header, NULL, 'false',
'true') AS "check",
'<a href=javascript:eliminarRegistro('
||a.id_matriz
||
')><img src="img/file_delete.png" alt="Eliminar" style="width:22px; height:22px;"></a>'
eliminar
FROM zmkt7352_cap_herramientas_head a
full outer join (SELECT *
FROM zmkt7352_rel_motor_herram
WHERE id_header = p_id_header)b
ON b.id_matriz_herr = a.id_matriz
WHERE a.delete_ind = 'N'
AND a.status_des = 'Y';
ELSE
IF ( p_id_header = 0000 ) THEN
OPEN c_matriz FOR
SELECT DISTINCT a.id_matriz AS id,
Decode(a.dealer_dr, 1, 'DR',
2, 'Dealer',
3, 'Planta',
'') AS dealer,
a.matriz_name AS name,
a.last_update_date AS lastUpdate,
'false' "check",
'<a href=javascript:eliminarRegistro('
||a.id_matriz
||
')><img src="img/file_delete.png" alt="Eliminar" style="width:22px; height:22px;"></a>'
eliminar
FROM zmkt7352_cap_herramientas_head a
WHERE delete_ind = 'N'
AND a.status_des = 'Y';
ELSE
OPEN c_matriz FOR
SELECT DISTINCT a.id_matriz AS id,
Decode(a.dealer_dr, 1, 'DR',
2, 'Dealer',
3, 'Planta',
'') AS dealer,
a.matriz_name AS name,
a.last_update_date AS lastUpdate,
'false' "check",
'<a href=javascript:eliminarRegistro('
||a.id_matriz
||
')><img src="img/file_delete.png" alt="Eliminar" style="width:22px; height:22px;"></a>'
eliminar
FROM zmkt7352_cap_herramientas_head a
WHERE delete_ind = 'N';
END IF;
END IF;
END obtener_matriz_teen_care_hmtas;
PROCEDURE Insertar_header_hmtas(
p_matriz_name zmkt7352_cap_herramientas_head.matriz_name%TYPE,
p_dealer_dr zmkt7352_cap_herramientas_head.dealer_dr%TYPE,
p_no_revision zmkt7352_cap_herramientas_head.no_revision%TYPE,
p_anio zmkt7352_cap_herramientas_head.anio%TYPE,
p_status_des zmkt7352_cap_herramientas_head.status_des%TYPE,
p_producto_nuevo zmkt7352_cap_herramientas_head.producto_nuevo%TYPE,
p_tipo_producto zmkt7352_cap_herramientas_head.tipo_producto%TYPE,
p_oem zmkt7352_cap_herramientas_head.oem%TYPE,
p_dr zmkt7352_cap_herramientas_head.dr%TYPE,
p_status_gral zmkt7352_cap_herramientas_head.status_gral%TYPE,
p_status_fin zmkt7352_cap_herramientas_head.status_fin%TYPE,
p_rango zmkt7352_cap_herramientas_head.rango%TYPE,
p_tipo_mer zmkt7352_cap_herramientas_head.tipo_mer%TYPE,
p_created_by zmkt7352_cap_herramientas_head.created_by%TYPE,
v_id OUT zmkt7352_cap_herramientas_head.id_matriz%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_cap_herramientas_head
(id_matriz,
matriz_name,
dealer_dr,
no_revision,
anio,
status_des,
producto_nuevo,
tipo_producto,
oem,
dr,
status_gral,
status_fin,
rango,
tipo_mer,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES ( herramientas_head_seq_id.NEXTVAL,
p_matriz_name,
p_dealer_dr,
p_no_revision,
p_anio,
p_status_des,
p_producto_nuevo,
p_tipo_producto,
p_oem,
p_dr,
p_status_gral,
p_status_fin,
p_rango,
p_tipo_mer,
SYSDATE,
p_created_by,
SYSDATE,
p_created_by,
'N' )
returning id_matriz INTO v_id;
END insertar_header_hmtas;
PROCEDURE Insertar_body_hmtas(p_id_matriz
zmkt7352_cap_herramientas_body.id_matriz%TYPE,
p_no_parte
zmkt7352_cap_herramientas_body.no_parte%TYPE,
p_no_parte_ant
zmkt7352_cap_herramientas_body.no_parte_ant%TYPE,
p_descripcion
zmkt7352_cap_herramientas_body.descripcion%TYPE,
p_ponderacion
zmkt7352_cap_herramientas_body.ponderacion%TYPE,
p_cm
zmkt7352_cap_herramientas_body.cm%TYPE,
p_comentarios
zmkt7352_cap_herramientas_body.comentarios%TYPE,
p_url
zmkt7352_cap_herramientas_body.url%TYPE,
p_precio_dlr
zmkt7352_cap_herramientas_body.precio_dlr%TYPE,
p_volumen_vta_1
zmkt7352_cap_herramientas_body.volumen_vta_1%TYPE,
p_volumen_vta_2
zmkt7352_cap_herramientas_body.volumen_vta_2%TYPE,
p_created_by
zmkt7352_cap_herramientas_body.created_by%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_cap_herramientas_body
(id_hmtas_bdy,
id_matriz,
no_parte,
no_parte_ant,
descripcion,
ponderacion,
cm,
comentarios,
url,
precio_dlr,
volumen_vta_1,
volumen_vta_2,
img,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES ( herramientas_body_seq_id.NEXTVAL,
p_id_matriz,
p_no_parte,
p_no_parte_ant,
p_descripcion,
p_ponderacion,
p_cm,
p_comentarios,
p_url,
p_precio_dlr,
p_volumen_vta_1,
p_volumen_vta_2,
Empty_blob(),
SYSDATE,
p_created_by,
SYSDATE,
p_created_by,
'N' );
END insertar_body_hmtas;
PROCEDURE Obtener_header_teen(p_id_matriz
zmkt7352_cap_infant_care_head.id_matriz%TYPE,
c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
SELECT id_matriz AS id,
matriz_name AS name,
dealer_dr AS dealer,
no_revision AS revision,
anio,
status_des AS status,
producto_nuevo,
tipo_producto,
oem,
dr,
status_gral,
status_fin,
rango,
tipo_mer
FROM zmkt7352_cap_infant_care_head
WHERE id_matriz = p_id_matriz
AND delete_ind = 'N';
END obtener_header_teen;
PROCEDURE Obtener_body_teen(p_id_matriz
zmkt7352_cap_infant_care_head.id_matriz%TYPE,
c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
SELECT id_infant_bdy,
id_matriz,
Nvl(no_parte, ' ') AS NO_PARTE,
Nvl(no_parte_ant, ' ') AS NO_PARTE_ANT,
Nvl(descripcion, ' ') AS DESCRIPCION,
Nvl(ponderacion, 0) AS PONDERACION,
Nvl(cm, 0) AS CM,
Nvl(comentarios, ' ') AS COMENTARIOS,
Nvl(url, ' ') AS URL,
Nvl(precio_dlr, 0) AS PRECIO_DLR,
Nvl(volumen_vta_1, 0) AS VOLUMEN_VTA_1,
Nvl(volumen_vta_2, 0) AS VOLUMEN_VTA_2
FROM zmkt7352_cap_infant_care_body
WHERE id_matriz = p_id_matriz
AND delete_ind = 'N';
END obtener_body_teen;
PROCEDURE Actualizar_header_teen(
p_id_matriz zmkt7352_cap_infant_care_body.id_matriz%TYPE,
p_matriz_name zmkt7352_cap_infant_care_head.matriz_name%TYPE,
p_dealer_dr zmkt7352_cap_infant_care_head.dealer_dr%TYPE,
p_no_revision zmkt7352_cap_infant_care_head.no_revision%TYPE,
p_anio zmkt7352_cap_infant_care_head.anio%TYPE,
p_status_des zmkt7352_cap_infant_care_head.status_des%TYPE,
p_producto_nuevo zmkt7352_cap_infant_care_head.producto_nuevo%TYPE,
p_tipo_producto zmkt7352_cap_infant_care_head.tipo_producto%TYPE,
p_oem zmkt7352_cap_infant_care_head.oem%TYPE,
p_dr zmkt7352_cap_infant_care_head.dr%TYPE,
p_status_gral zmkt7352_cap_infant_care_head.status_gral%TYPE,
p_status_fin zmkt7352_cap_infant_care_head.status_fin%TYPE,
p_rango zmkt7352_cap_infant_care_head.rango%TYPE,
p_tipo_mer zmkt7352_cap_infant_care_head.tipo_mer%TYPE)
IS
BEGIN
UPDATE zmkt7352_cap_infant_care_head
SET matriz_name = p_matriz_name,
dealer_dr = p_dealer_dr,
no_revision = p_no_revision,
anio = p_anio,
tipo_producto = p_tipo_producto,
status_des = p_status_des,
producto_nuevo = p_producto_nuevo,
oem = p_oem,
dr = p_dr,
tipo_mer = p_tipo_mer,
rango = p_rango,
last_update_date = SYSDATE,
last_update_by = 'system'
WHERE id_matriz = p_id_matriz;
END actualizar_header_teen;
PROCEDURE Actualizar_body_teen(p_id_infant_bdy
zmkt7352_cap_infant_care_body.id_infant_bdy%TYPE,
p_no_parte
zmkt7352_cap_infant_care_body.no_parte%TYPE,
p_no_parte_ant
zmkt7352_cap_infant_care_body.no_parte_ant%TYPE,
p_descripcion
zmkt7352_cap_infant_care_body.descripcion%TYPE,
p_ponderacion
zmkt7352_cap_infant_care_body.ponderacion%TYPE,
p_cm
zmkt7352_cap_infant_care_body.cm%TYPE,
p_comentarios
zmkt7352_cap_infant_care_body.comentarios%TYPE,
p_url
zmkt7352_cap_infant_care_body.url%TYPE,
p_precio_dlr
zmkt7352_cap_infant_care_body.precio_dlr%TYPE,
p_volumen_vta_1
zmkt7352_cap_infant_care_body.volumen_vta_1%TYPE,
p_volumen_vta_2
zmkt7352_cap_infant_care_body.volumen_vta_2%TYPE)
IS
BEGIN
UPDATE zmkt7352_cap_infant_care_body
SET no_parte = p_no_parte,
no_parte_ant = p_no_parte_ant,
descripcion = p_descripcion,
ponderacion = p_ponderacion,
cm = p_cm,
comentarios = p_comentarios,
url = p_url,
precio_dlr = p_precio_dlr,
volumen_vta_1 = p_volumen_vta_1,
volumen_vta_2 = p_volumen_vta_2,
last_update_date = SYSDATE,
last_update_by = 'system'
WHERE id_infant_bdy = p_id_infant_bdy;
END actualizar_body_teen;
----Herramientas
PROCEDURE Obtener_header_hmtas(p_id_matriz
zmkt7352_cap_herramientas_head.id_matriz%TYPE,
c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
SELECT id_matriz AS id,
matriz_name AS name,
dealer_dr AS dealer,
no_revision AS revision,
anio,
status_des AS status,
producto_nuevo,
tipo_producto,
oem,
dr,
status_gral,
status_fin,
rango,
tipo_mer
FROM zmkt7352_cap_herramientas_head
WHERE id_matriz = p_id_matriz
AND delete_ind = 'N';
END obtener_header_hmtas;
PROCEDURE Obtener_body_hmtas(p_id_matriz
zmkt7352_cap_herramientas_head.id_matriz%TYPE,
c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
SELECT id_hmtas_bdy,
id_matriz,
Nvl(no_parte, ' ') AS NO_PARTE,
Nvl(no_parte_ant, ' ') AS NO_PARTE_ANT,
Nvl(descripcion, ' ') AS DESCRIPCION,
Nvl(ponderacion, 0) AS PONDERACION,
Nvl(cm, 0) AS CM,
Nvl(comentarios, ' ') AS COMENTARIOS,
Nvl(url, ' ') AS URL,
Nvl(precio_dlr, 0) AS PRECIO_DLR,
Nvl(volumen_vta_1, 0) AS VOLUMEN_VTA_1,
Nvl(volumen_vta_2, 0) AS VOLUMEN_VTA_2
FROM zmkt7352_cap_herramientas_body
WHERE id_matriz = p_id_matriz
AND delete_ind = 'N';
END obtener_body_hmtas;
PROCEDURE Actualizar_header_hmtas(
p_id_matriz zmkt7352_cap_herramientas_head.id_matriz%TYPE,
p_matriz_name zmkt7352_cap_herramientas_head.matriz_name%TYPE,
p_dealer_dr zmkt7352_cap_herramientas_head.dealer_dr%TYPE,
p_no_revision zmkt7352_cap_herramientas_head.no_revision%TYPE,
p_anio zmkt7352_cap_herramientas_head.anio%TYPE,
p_status_des zmkt7352_cap_herramientas_head.status_des%TYPE,
p_producto_nuevo zmkt7352_cap_herramientas_head.producto_nuevo%TYPE,
p_tipo_producto zmkt7352_cap_herramientas_head.tipo_producto%TYPE,
p_oem zmkt7352_cap_herramientas_head.oem%TYPE,
p_dr zmkt7352_cap_herramientas_head.dr%TYPE,
p_status_gral zmkt7352_cap_herramientas_head.status_gral%TYPE,
p_status_fin zmkt7352_cap_herramientas_head.status_fin%TYPE,
p_rango zmkt7352_cap_herramientas_head.rango%TYPE,
p_tipo_mer zmkt7352_cap_herramientas_head.tipo_mer%TYPE)
IS
BEGIN
UPDATE zmkt7352_cap_herramientas_head
SET matriz_name = p_matriz_name,
dealer_dr = p_dealer_dr,
no_revision = p_no_revision,
anio = p_anio,
tipo_producto = p_tipo_producto,
status_des = p_status_des,
producto_nuevo = p_producto_nuevo,
oem = p_oem,
dr = p_dr,
tipo_mer = p_tipo_mer,
rango = p_rango,
last_update_date = SYSDATE,
last_update_by = 'system'
WHERE id_matriz = p_id_matriz;
END actualizar_header_hmtas;
PROCEDURE Actualizar_body_hmtas(p_id_hmtas_bdy
zmkt7352_cap_herramientas_body.id_hmtas_bdy%TYPE,
p_no_parte
zmkt7352_cap_herramientas_body.no_parte%TYPE,
p_no_parte_ant
zmkt7352_cap_herramientas_body.no_parte_ant%TYPE,
p_descripcion
zmkt7352_cap_herramientas_body.descripcion%TYPE,
p_ponderacion
zmkt7352_cap_herramientas_body.ponderacion%TYPE,
p_cm
zmkt7352_cap_herramientas_body.cm%TYPE,
p_comentarios
zmkt7352_cap_herramientas_body.comentarios%TYPE,
p_url
zmkt7352_cap_herramientas_body.url%TYPE,
p_precio_dlr
zmkt7352_cap_herramientas_body.precio_dlr%TYPE,
p_volumen_vta_1
zmkt7352_cap_herramientas_body.volumen_vta_1%TYPE,
p_volumen_vta_2
zmkt7352_cap_herramientas_body.volumen_vta_2%TYPE)
IS
BEGIN
UPDATE zmkt7352_cap_herramientas_body
SET no_parte = p_no_parte,
no_parte_ant = p_no_parte_ant,
descripcion = p_descripcion,
ponderacion = p_ponderacion,
cm = p_cm,
comentarios = p_comentarios,
url = p_url,
precio_dlr = p_precio_dlr,
volumen_vta_1 = p_volumen_vta_1,
volumen_vta_2 = p_volumen_vta_2,
last_update_date = SYSDATE,
last_update_by = 'system'
WHERE id_hmtas_bdy = p_id_hmtas_bdy;
END actualizar_body_hmtas;
PROCEDURE Update_del_ind_hmtas(p_id_hmtas_bdy
zmkt7352_cap_herramientas_body.id_hmtas_bdy%TYPE)
IS
BEGIN
UPDATE zmkt7352_cap_herramientas_body
SET delete_ind = 'Y'
WHERE id_hmtas_bdy = p_id_hmtas_bdy;
END update_del_ind_hmtas;
PROCEDURE Update_del_ind_tc(p_id_infant_bdy
zmkt7352_cap_infant_care_body.id_infant_bdy%TYPE)
IS
BEGIN
--update ZMKT7352_CAP_INFANT_CARE_BODY set DELETE_IND='Y' where ID_INFANT_BDY=p_ID_INFANT_BDY;
DELETE FROM zmkt7352_rel_matriz_dealer
WHERE ( id_matriz, no_parte ) IN(SELECT id_matriz,
no_parte
FROM zmkt7352_cap_infant_care_body
WHERE id_infant_bdy = p_id_infant_bdy);
DELETE FROM zmkt7352_cap_infant_care_body
WHERE id_infant_bdy = p_id_infant_bdy;
END update_del_ind_tc;
PROCEDURE Lista_motores(c_motores OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_motores FOR
SELECT p.id_participacion_motor,
(SELECT d.service_provider_name DR
FROM zmkt7352_dr_dealers_ebu d
WHERE d.sp_code = p.dr) AS DR,
(SELECT m.motor
FROM zmkt7352_motores m
WHERE m.id_motores = p.matriz_name) AS matriz_name,
(SELECT r.rango_n
FROM zmkt7352_rango_sistema r
WHERE r.id_rango = p.rango) AS RANGO,
p.parmotormin,
p.parmotormax,
p.mec_req
FROM zmkt7352_participacion_motores p
WHERE delete_ind = 'N';
END lista_motores;
PROCEDURE Update_del_ind_motor(p_id_participacion_motor
zmkt7352_participacion_motores.id_participacion_motor%TYPE)
IS
BEGIN
UPDATE zmkt7352_participacion_motores
SET delete_ind = 'Y'
WHERE id_participacion_motor = p_id_participacion_motor;
END update_del_ind_motor;
PROCEDURE Insertar_motor(p_matriz_name
zmkt7352_participacion_motores.matriz_name%TYPE,
p_rango
zmkt7352_participacion_motores.rango%TYPE,
p_dr zmkt7352_participacion_motores.dr%TYPE,
p_mec_req
zmkt7352_participacion_motores.mec_req%TYPE,
p_created_by
zmkt7352_participacion_motores.created_by%TYPE,
p_parmotormin
zmkt7352_participacion_motores.parmotormin%TYPE,
p_parmotormax
zmkt7352_participacion_motores.parmotormax%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_participacion_motores
(id_participacion_motor,
matriz_name,
rango,
dr,
mec_req,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind,
parmotormin,
parmotormax)
VALUES ( zmkt7352_sec_id_particip_motor.NEXTVAL,
p_matriz_name,
p_rango,
p_dr,
p_mec_req,
SYSDATE,
p_created_by,
SYSDATE,
p_created_by,
'N',
p_parmotormin,
p_parmotormax );
END insertar_motor;
PROCEDURE Obtener_lista_dr(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
--select SP_CODE, SERVICE_PROVIDER_NAME from ZMKT7352_DR_DEALERS_EBU where SP_CODE = RESPONSIBLE_BRANCH_CODE
SELECT sp_code AS KEY,
service_provider_name AS value
FROM zmkt7352_dr_dealers_ebu
WHERE sp_code = responsible_branch_code
ORDER BY 2;
END obtener_lista_dr;
PROCEDURE Obtener_lista_motores(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT id_motores AS KEY,
motor AS value
FROM zmkt7352_motores
WHERE delete_ind = 'N'
ORDER BY 2;
END obtener_lista_motores;
PROCEDURE Obtener_lista_rango(p_id zmkt7352_motores.id_motores%TYPE,
c_lista OUT SYS_REFCURSOR)
IS
BEGIN
IF ( p_id IS NULL
OR p_id = '' ) THEN
OPEN c_lista FOR
SELECT r.id_rango AS KEY,
r.rango_n AS value
FROM zmkt7352_rango_sistema r;
ELSE
OPEN c_lista FOR
SELECT r.id_rango AS KEY,
r.rango_n AS value
FROM zmkt7352_rango_sistema r,
zmkt7352_motores m
WHERE m.id_motores = p_id
AND m.rango = r.id_rango;
END IF;
END obtener_lista_rango;
PROCEDURE Update_par_motor_min(p_id
zmkt7352_participacion_motores.id_participacion_motor%TYPE,
p_valor
zmkt7352_participacion_motores.parmotormin%TYPE)
IS
BEGIN
UPDATE zmkt7352_participacion_motores
SET parmotormin = p_valor
WHERE id_participacion_motor = p_id;
END update_par_motor_min;
PROCEDURE Update_par_motor_max(p_id
zmkt7352_participacion_motores.id_participacion_motor%TYPE,
p_valor
zmkt7352_participacion_motores.parmotormax%TYPE)
IS
BEGIN
UPDATE zmkt7352_participacion_motores
SET parmotormax = p_valor
WHERE id_participacion_motor = p_id;
END update_par_motor_max;
PROCEDURE Update_mec_req(p_id
zmkt7352_participacion_motores.id_participacion_motor%TYPE,
p_valor zmkt7352_participacion_motores.mec_req%TYPE)
IS
BEGIN
UPDATE zmkt7352_participacion_motores
SET mec_req = p_valor
WHERE id_participacion_motor = p_id;
END update_mec_req;
PROCEDURE Lista_evaluaciones(c_evaluaciones OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_evaluaciones FOR
SELECT id_evaluacion,
num_eval,
tipo_eval AS tipo,
--decode(TIPO_EVAL,1,'EVALUACION',2,'PRE EVALUACION') as tipo,
anio_eval,
To_char(fec_ini_eval, 'DD/MM/YYYY') AS fecha_inicio,
To_char(fec_fin_eval, 'DD/MM/YYYY') AS fecha_fin,
status
FROM zmkt7352_evaluaciones
WHERE delete_ind = 'N';
END lista_evaluaciones;
PROCEDURE Guardar_evaluaciones(p_num_eval
zmkt7352_evaluaciones.num_eval%TYPE,
p_anio_eval
zmkt7352_evaluaciones.anio_eval%TYPE,
p_fec_ini_eval
zmkt7352_evaluaciones.fec_ini_eval%TYPE,
p_fec_fin_eval
zmkt7352_evaluaciones.fec_fin_eval%TYPE,
p_tipo_eval
zmkt7352_evaluaciones.tipo_eval%TYPE,
p_created_by
zmkt7352_evaluaciones.created_by%TYPE,
p_status
zmkt7352_evaluaciones.status%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_evaluaciones
(id_evaluacion,
num_eval,
anio_eval,
fec_ini_eval,
fec_fin_eval,
tipo_eval,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind,
status)
VALUES ( zmkt7352_sec_id_evaluacion.NEXTVAL,
p_num_eval,
p_anio_eval,
p_fec_ini_eval,
p_fec_fin_eval,
p_tipo_eval,
SYSDATE,
p_created_by,
SYSDATE,
p_created_by,
'N',
p_status );
END guardar_evaluaciones;
PROCEDURE Update_num_eval(p_id zmkt7352_evaluaciones.id_evaluacion%TYPE,
p_valor zmkt7352_evaluaciones.num_eval%TYPE)
IS
BEGIN
UPDATE zmkt7352_evaluaciones
SET num_eval = p_valor
WHERE id_evaluacion = p_id;
END update_num_eval;
PROCEDURE Update_tipo_eval(p_id zmkt7352_evaluaciones.id_evaluacion%TYPE,
p_valor zmkt7352_evaluaciones.tipo_eval%TYPE)
IS
BEGIN
UPDATE zmkt7352_evaluaciones
SET tipo_eval = p_valor
WHERE id_evaluacion = p_id;
END update_tipo_eval;
PROCEDURE Update_anio_eval(p_id zmkt7352_evaluaciones.id_evaluacion%TYPE,
p_valor zmkt7352_evaluaciones.anio_eval%TYPE)
IS
BEGIN
UPDATE zmkt7352_evaluaciones
SET anio_eval = p_valor
WHERE id_evaluacion = p_id;
END update_anio_eval;
PROCEDURE Update_fec_ini(p_id zmkt7352_evaluaciones.id_evaluacion%TYPE,
p_valor zmkt7352_evaluaciones.fec_ini_eval%TYPE)
IS
BEGIN
UPDATE zmkt7352_evaluaciones
SET fec_ini_eval = p_valor
WHERE id_evaluacion = p_id;
END update_fec_ini;
PROCEDURE Update_fec_fin(p_id zmkt7352_evaluaciones.id_evaluacion%TYPE,
p_valor zmkt7352_evaluaciones.fec_fin_eval%TYPE)
IS
BEGIN
UPDATE zmkt7352_evaluaciones
SET fec_fin_eval = p_valor
WHERE id_evaluacion = p_id;
END update_fec_fin;
PROCEDURE Update_estatus(p_id zmkt7352_evaluaciones.id_evaluacion%TYPE,
p_valor zmkt7352_evaluaciones.status%TYPE)
IS
BEGIN
UPDATE zmkt7352_evaluaciones
SET status = p_valor
WHERE id_evaluacion = p_id;
END update_estatus;
PROCEDURE Agregar_modulo(p_modulo zmkt7352_modulo.modulo%TYPE,
p_ponderacion zmkt7352_modulo.ponderacion%TYPE,
p_created_by zmkt7352_modulo.created_by%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_modulo
(id_modulo,
modulo,
ponderacion,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES ( modulo_seq_id.NEXTVAL,
p_modulo,
p_ponderacion,
SYSDATE,
p_created_by,
SYSDATE,
p_created_by,
'N' );
END agregar_modulo;
PROCEDURE Lista_modulos(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT id_modulo,
modulo,
Nvl(ponderacion, 0) AS PONDERACION
FROM zmkt7352_modulo
WHERE delete_ind = 'N';
END lista_modulos;
PROCEDURE Actualizar_modulo(p_id_modulo zmkt7352_modulo.id_modulo%TYPE,
p_ponderacion zmkt7352_modulo.ponderacion%TYPE,
p_last_update_by zmkt7352_modulo.created_by%TYPE)
IS
BEGIN
UPDATE zmkt7352_modulo
SET ponderacion = p_ponderacion,
last_update_by = p_last_update_by,
last_update_date = SYSDATE
WHERE id_modulo = p_id_modulo;
END actualizar_modulo;
PROCEDURE Agregar_submodulo(p_id_modulo zmkt7352_submodulo.id_modulo%TYPE,
p_submodulo zmkt7352_submodulo.submodulo%TYPE,
p_ponderacion zmkt7352_submodulo.ponderacion%TYPE,
p_created_by zmkt7352_submodulo.created_by%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_submodulo
(id_submodulo,
id_modulo,
submodulo,
ponderacion,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES ( submodulo_seq_id.NEXTVAL,
p_id_modulo,
p_submodulo,
p_ponderacion,
SYSDATE,
p_created_by,
SYSDATE,
p_created_by,
'N' );
END agregar_submodulo;
PROCEDURE Lista_submodulos(p_id_modulo zmkt7352_submodulo.id_modulo%TYPE,
c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT s.id_submodulo,
(SELECT m1.modulo
FROM zmkt7352_modulo m1
WHERE m1.id_modulo = p_id_modulo) AS modulo,
(SELECT m2.ponderacion
FROM zmkt7352_modulo m2
WHERE m2.id_modulo = p_id_modulo) AS ponderacion_modulo,
s.submodulo,
Nvl(s.ponderacion, 0) AS ponderacion_submodulo
FROM zmkt7352_submodulo s
WHERE s.id_modulo = p_id_modulo;
END lista_submodulos;
PROCEDURE Actualizar_submodulo(p_id_submodulo
zmkt7352_submodulo.id_submodulo%TYPE,
p_ponderacion
zmkt7352_submodulo.ponderacion%TYPE,
p_last_update_by
zmkt7352_submodulo.created_by%TYPE)
IS
BEGIN
UPDATE zmkt7352_submodulo
SET ponderacion = p_ponderacion,
last_update_date = SYSDATE,
last_update_by = p_last_update_by
WHERE id_submodulo = p_id_submodulo;
END actualizar_submodulo;
PROCEDURE Obtener_lista_oem(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT id_oem AS KEY,
Trim(oem) AS value
FROM zmkt7352_oem
WHERE delete_ind = 'N';
END obtener_lista_oem;
PROCEDURE Obtener_lista_para(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT id_dealer_dr AS KEY,
Trim(desc_dealer_dr) AS value
FROM zmkt7352_para_dealer_dr
WHERE delete_ind = 'N';
END obtener_lista_para;
PROCEDURE Obtener_lista_rango_inf(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT id_rango AS KEY,
Trim(rango_n) AS value
FROM zmkt7352_rango_sistema
WHERE delete_ind = 'N';
END obtener_lista_rango_inf;
PROCEDURE Obtener_lista_mercado(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
SELECT id_mercado AS KEY,
Trim(mercado_n) AS value
FROM zmkt7352_cap_mercado_sistema
WHERE delete_ind = 'N';
END obtener_lista_mercado;
PROCEDURE Obtener_matriz_infant_teen(c_matriz OUT SYS_REFCURSOR,
p_id_header IN
zmkt7352_rel_motor_matriz.id_header%TYPE)
IS
BEGIN
IF ( p_id_header IS NOT NULL
OR p_id_header <> '' ) THEN
OPEN c_matriz FOR
SELECT DISTINCT a.id_matriz AS id,
dealer_dr AS dealer,
matriz_name AS name,
a.last_update_date AS lastUpdate,
Decode(id_header, NULL, 'false',
'true') AS "check"
FROM zmkt7352_cap_infant_care_head a
full outer join (SELECT *
FROM zmkt7352_rel_motor_matriz
WHERE id_header = p_id_header)b
ON b.id_matriz = a.id_matriz
WHERE a.delete_ind = 'N'
AND a.status_des = 'Y';
ELSE
OPEN c_matriz FOR
SELECT DISTINCT a.id_matriz AS id,
dealer_dr AS dealer,
matriz_name AS name,
a.last_update_date AS lastUpdate,
'false' "check"
FROM zmkt7352_cap_infant_care_head a
WHERE delete_ind = 'N'
AND a.status_des = 'Y';
END IF;
END obtener_matriz_infant_teen;
PROCEDURE Obtener_cursos(p_id_puesto zmkt7352_cursos_promo.id_puesto%TYPE,
c_lista OUT SYS_REFCURSOR,
p_id_header IN
zmkt7352_rel_motor_matriz.id_header%TYPE)
IS
BEGIN
IF ( p_id_header IS NOT NULL
OR p_id_header <> '' ) THEN
OPEN c_lista FOR
SELECT DISTINCT program_id,
program_name,
Decode(id_header, NULL, 'false',
'true') AS "check"
FROM zmkt7352_cursos_promo a
full outer join (SELECT *
FROM zmkt7352_rel_motor_cursos
WHERE id_header = p_id_header)b
ON To_char(b.id_curso) = To_char(a.program_id)
WHERE id_puesto = p_id_puesto
ORDER BY program_name;
ELSE
OPEN c_lista FOR
SELECT DISTINCT program_id,
program_name,
'false' "check"
FROM zmkt7352_cursos_promo a
WHERE id_puesto = p_id_puesto
ORDER BY program_name;
END IF;
END obtener_cursos;
PROCEDURE Insertar_mot_herr(p_id_header
zmkt7352_rel_motor_herram.id_header%TYPE,
p_id_matriz_herr
zmkt7352_rel_motor_herram.id_matriz_herr%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_rel_motor_herram
(id_header,
id_herram_header,
id_matriz_herr)
VALUES ( p_id_header,
rel_mot_herr_seq_id.NEXTVAL,
p_id_matriz_herr );
END insertar_mot_herr;
PROCEDURE Insertar_mot_matriz(p_id_header
zmkt7352_rel_motor_matriz.id_header%TYPE,
p_id_matriz
zmkt7352_rel_motor_matriz.id_matriz%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_rel_motor_matriz
(id_header,
id_infant_header,
id_matriz)
VALUES ( p_id_header,
rel_mot_mat_seq_id.NEXTVAL,
p_id_matriz );
END insertar_mot_matriz;
PROCEDURE Insertar_mot_cursos(p_id_header
zmkt7352_rel_motor_cursos.id_header%TYPE,
p_id_curso
zmkt7352_rel_motor_cursos.id_curso%TYPE,
p_puesto
zmkt7352_rel_motor_cursos.puesto%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_rel_motor_cursos
(id_header,
id_curso,
puesto,
id_mot_cur)
VALUES ( p_id_header,
p_id_curso,
p_puesto,
rel_mot_cur_seq_id.NEXTVAL );
END insertar_mot_cursos;
PROCEDURE Obtener_partes_individual(p_id_matriz
zmkt7352_cap_infant_care_head.id_matriz%TYPE,
c_matriz OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_matriz FOR
SELECT DISTINCT id_matriz AS id,
dealer_dr AS dealer,
matriz_name AS name,
last_update_date AS lastUpdate
FROM zmkt7352_cap_infant_care_head
WHERE delete_ind = 'N'
ORDER BY matriz_name;
END obtener_partes_individual;
PROCEDURE Insertar_conf_mot_header(p_id_motor
zmkt7352_rel_motor_header.id_motor%TYPE,
v_id OUT
zmkt7352_rel_motor_header.id_header%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_rel_motor_header
(id_header,
id_motor)
VALUES ( rel_mot_head_seq_id.NEXTVAL,
p_id_motor )
returning id_header INTO v_id;
END insertar_conf_mot_header;
PROCEDURE Calificado_jefe_gerente(c_jefe_taller OUT SYS_REFCURSOR,
p_code IN
zmkt7352_promotion.sp_code%TYPE,
p_puesto IN NUMBER)
IS
worker1 VARCHAR2(100);
worker2 VARCHAR2(100);
v_cursor SYS_REFCURSOR;
BEGIN
IF p_puesto = 2 THEN
OPEN c_jefe_taller FOR
SELECT promotion_id id_promos,
nombre,
jobtitle motor,
date_passed,
100 posible,
SUM(aprobado) * 100 Porcentaje
FROM (SELECT DISTINCT promotion_id,
jobtitle,
nombre,
date_passed,
aprobado
FROM (SELECT jobtitle,
promotion_id,
nombre,
date_passed,
CASE
WHEN Instr(cursos, id_curso) > 0 THEN 1
ELSE 0
END aprobado
FROM (SELECT DISTINCT a.id_motores_oem,
a.motor,
c.sp_code
FROM zmkt.zmkt7352_motores_oem a
inner join zmkt.zmkt7352_oem b
ON a.oem = To_char(b.id_oem)
inner join zmkt.zmkt7352_dr_dealers_ebu
c
ON b.id_oem =
c.mexico_equivalent_oem
inner join zmkt.zmkt7352_motores d
ON d.id_motores =
a.id_motores_oem
full outer join
zmkt.zmkt7352_rel_perfil_motor
e
ON
e.id_motor = a.id_motores_oem
AND c.sp_code = e.sp_code
WHERE c.sp_code = p_code
AND e.id_motor IS NOT NULL) a
inner join zmkt.zmkt7352_rel_motor_header f
ON f.id_motor = a.id_motores_oem
inner join zmkt.zmkt7352_rel_motor_cursos g
ON f.id_header = g.id_header
full outer join (SELECT
promotion_id,
date_passed,
first_name
||' '
||last_name
AS
nombre,
Listagg(program_id, ',')
within GROUP (ORDER BY
program_id) AS
cursos,
sp_code,
jobtitle
FROM zmkt.zmkt7352_promotion
WHERE sp_code = p_code
GROUP BY first_name,
last_name,
sp_code,
jobtitle,
date_passed,
promotion_id) h
ON h.sp_code = a.sp_code
WHERE puesto = 1
ORDER BY motor))
GROUP BY promotion_id,
jobtitle,
nombre,
date_passed
HAVING SUM(aprobado) = 1
OR jobtitle IN( 'Warranty' )
ORDER BY nombre;
ELSE
IF p_puesto = 2 THEN
worker1 := 'Service - Management';
worker2 := 'Administration or Non Technical';
ELSE
worker1 := 'Service - Management';
worker2 := 'Service - Management';
END IF;
OPEN c_jefe_taller FOR
SELECT DISTINCT motor,
nombre,--100 posible,
CASE
WHEN Instr(cursos, id_curso) > 0 THEN 100
ELSE /*0*/100
END AS obtenido,
promotion_id
/*, JOBTITLE,
id_motores_oem, motor, id_curso, a.sp_code,cursos, nombre */
FROM (SELECT DISTINCT a.id_motores_oem,
a.motor,
c.sp_code
FROM zmkt7352_motores_oem a
inner join zmkt7352_oem b
ON a.oem = To_char(b.id_oem)
inner join zmkt7352_dr_dealers_ebu c
ON b.id_oem = c.mexico_equivalent_oem
inner join zmkt7352_motores d
ON d.id_motores = a.id_motores_oem
full outer join zmkt7352_rel_perfil_motor e
ON e.id_motor = a.id_motores_oem
AND c.sp_code = e.sp_code
WHERE c.sp_code = p_code
AND e.id_motor IS NOT NULL
-- and a.id_motores_oem='1105'
) a
inner join zmkt7352_rel_motor_header f
ON f.id_motor = a.id_motores_oem
inner join zmkt7352_rel_motor_cursos g
ON f.id_header = g.id_header
full outer join (SELECT first_name
||' '
||last_name nombre,
Wm_concat(program_id) cursos,
sp_code,
jobtitle,
promotion_id
FROM zmkt7352_promotion
WHERE sp_code = p_code
GROUP BY first_name,
last_name,
sp_code,
jobtitle,
promotion_id) h
ON h.sp_code = a.sp_code
WHERE puesto = p_puesto
AND jobtitle IN( worker1, worker2 )
ORDER BY motor;
END IF;
--fetch c_jefe_taller into v_cursor;
IF c_jefe_taller%rowcount = 0 THEN
OPEN c_jefe_taller FOR
SELECT '0' Porcentaje,
'' DATE_PASSED,
'' id_promos,
'' promotion_id,
'' motor,
'' nombre,
'0' obtenido,
'100' posible
FROM dual;
END IF;
/*
puesto1 varchar2(50);
puesto2 varchar2(50);
Begin
if p_puesto =1 then --Jefe de Taller
puesto1:='Service - Management';
puesto2:='Administration or Non Technical';
end if;
if p_puesto =2 then --Admin Garantias
puesto1:='Warranty';
puesto2:='Warranty';
end if;
if p_puesto =3 then --Gerente de servicio
puesto1:='Service - Management';
puesto2:='Service - Management';
end if;
Open c_jefe_taller For
Select Distinct p.PROMOTION_ID id_promos, p.DATE_PASSED, M.Motor, P.SP_Code, C.ID_Curso, P.First_Name || ' ' || P.Middle_Name || ' ' || P.Last_Name nombre,
(Case
When P.JobTitle In (puesto1,puesto2) And P.Program_Id Is Not Null Then '100%'
-- When P.JobTitle In ('Warranty') And P.Program_Id Is Not Null Then '100%'
Else '0%'
End) As Porcentaje
From ZMKT7352_REL_MOTOR_Header H, ZMKT7352_REL_MOTOR_CURSOS C Full Outer Join ZMKT7352_PROMOTION P
On C.ID_Curso = P.Program_ID,
(Select a.id_motores_oem, a.motor, 'x' as "CHECK"
From ZMKT7352_MOTORES_OEM A Inner Join ZMKT7352_OEM B On A.oem = to_char(b.id_oem)
Inner Join ZMKT7352_DR_DEALERS_EBU C On B.id_oem = c.MEXICO_EQUIVALENT_OEM
Where c.sp_code = p_code) M
Where P.SP_Code = p_code And
H.Id_Motor = M.Id_Motores_Oem And
H.ID_Header = C.ID_Header
and ID_PROMOS is not null
and trim(P.First_Name) is not null;
*/
END calificado_jefe_gerente;
PROCEDURE Preeva_qtymec(c_qty_mec OUT SYS_REFCURSOR,
p_sp_code IN zmkt7352_promotion.sp_code%TYPE)
IS
BEGIN
OPEN c_qty_mec FOR
SELECT T.motor AS Motor,
Count(P.last_name) AS Cantidad
FROM zmkt7352_promotion P,
zmkt7352_dr_dealers_ebu D,
zmkt7352_motores_oem O,
zmkt7352_motores T
WHERE D.sp_code = p_sp_code
AND P.jobtitle IN ( 'Service - Technician (Engine)' )
AND P.sp_code = D.sp_code
AND D.mexico_equivalent_oem = O.oem
AND O.id_motores_oem = T.id_motores
GROUP BY T.motor
ORDER BY 1;
END preeva_qtymec;
PROCEDURE Preeva_nombremec(c_nombre_mec OUT SYS_REFCURSOR,
p_sp_code IN zmkt7352_promotion.sp_code%TYPE)
IS
BEGIN
OPEN c_nombre_mec FOR
SELECT DISTINCT T.motor AS Motor,
P.first_name
|| P.last_name AS Nombre,
promotion_id
FROM zmkt7352_promotion P,
zmkt7352_dr_dealers_ebu D,
zmkt7352_motores_oem O,
zmkt7352_motores T
WHERE D.sp_code = p_sp_code
AND P.jobtitle IN ( 'Service - Technician (Engine)' )
AND P.sp_code = D.sp_code
AND D.mexico_equivalent_oem = O.oem
AND O.id_motores_oem = T.id_motores
ORDER BY 1,
2;
END preeva_nombremec;
PROCEDURE Preeva_jefeserv(c_jefe_serv OUT SYS_REFCURSOR,
p_sp_code IN zmkt7352_promotion.sp_code%TYPE)
IS
BEGIN
OPEN c_jefe_serv FOR
SELECT P.first_name
|| P.last_name AS Nombre,
T.motor,
P.promotion_id,
( CASE
WHEN P.jobtitle IN ( 'Service - Management' ) THEN
'Gerente de Servicio'
WHEN P.jobtitle IN ( 'Administration or Non Technical' ) THEN
'Jefe de Taller'
END ) AS Puesto
FROM zmkt7352_promotion P,
zmkt7352_rel_motor_cursos M,
zmkt7352_motores T
WHERE P.sp_code = p_sp_code
AND P.program_id = M.id_curso
AND M.id_mot_cur = T.id_motores;
END preeva_jefeserv;
PROCEDURE Preeva_garantias(c_garantias OUT SYS_REFCURSOR,
p_sp_code IN zmkt7352_promotion.sp_code%TYPE)
IS
BEGIN
OPEN c_garantias FOR
SELECT promotion_id,
nombre,
jobtitle Motor,
To_char(date_passed, 'DD/MM/YY') date_passed,
program_id program_name
FROM (SELECT DISTINCT promotion_id,
jobtitle,
sp_code,
nombre,
date_passed,
aprobado,
program_id
FROM (SELECT jobtitle,
promotion_id,
nombre,
date_passed,
a.sp_code,
CASE
WHEN Instr(cursos, id_curso) > 0 THEN 1
ELSE 0
END aprobado,
program_id
FROM (SELECT DISTINCT a.id_motores_oem,
a.motor,
c.sp_code
FROM zmkt7352_motores_oem a
inner join zmkt7352_oem b
ON a.oem = To_char(b.id_oem)
inner join zmkt7352_dr_dealers_ebu c
ON b.id_oem =
c.mexico_equivalent_oem
inner join zmkt7352_motores d
ON d.id_motores =
a.id_motores_oem
full outer join zmkt7352_rel_perfil_motor
e
ON
e.id_motor = a.id_motores_oem
AND c.sp_code = e.sp_code
WHERE c.sp_code = 31736
AND e.id_motor IS NOT NULL) a
inner join zmkt7352_rel_motor_header f
ON f.id_motor = a.id_motores_oem
inner join zmkt7352_rel_motor_cursos g
ON f.id_header = g.id_header
full outer join (SELECT promotion_id,
date_passed,
first_name
||' '
||last_name
Nombre,
Listagg(program_id, ',')
within GROUP (ORDER BY
program_id)
cursos,
sp_code,
jobtitle,
program_id
FROM zmkt7352_promotion
WHERE sp_code = 31736
GROUP BY first_name,
last_name,
sp_code,
jobtitle,
date_passed,
promotion_id,
program_id) h
ON h.sp_code = a.sp_code
WHERE puesto = 1
AND jobtitle IN( 'Warranty' )
AND ROWNUM = 1
ORDER BY motor))
GROUP BY promotion_id,
jobtitle,
nombre,
date_passed,
sp_code,
program_id
HAVING SUM(aprobado) = 1
OR jobtitle IN( 'Warranty' );
END preeva_garantias;
PROCEDURE Modulos_usuario(c_usuario_modulos OUT SYS_REFCURSOR,
p_wwid IN zmkt7352_usuario.wwid%TYPE)
IS
BEGIN
OPEN c_usuario_modulos FOR
SELECT Upper(U.wwid) WWID,
U.nombre,
P.id_grupo,/*S.SP_Code*/
'--' SP_Code,/* DR.RESPONSIBLE_BRANCH_CODE, */
G.descripcion,
A.id_programa,
R.jsp_name,
M.nombre
FROM zmkt7352_usuario U,
zmkt7352_groupperuser P,
zmkt7352_grupo G,
zmkt7352_acceso A,
zmkt7352_programa R,
zmkt7352_menu M
--, ZMKT7352_USUARIO_SPCODE S,ZMKT7352_DR_DEALERS_EBU dr
WHERE U.wwid = Upper(p_wwid)
AND U.wwid = P.wwid
AND P.id_grupo = G.id_grupo
AND G.id_grupo = A.id_grupo
AND A.id_programa = R.id_programa
AND R.id_menu = M.id_menu
-- And U.WWID = S.WWID
--AND S.SP_CODE = DR.SP_CODE
;
END modulos_usuario;
PROCEDURE Insertar_promotion(p_id_promos IN VARCHAR2,
p_program_id IN VARCHAR2,
p_abo_name IN VARCHAR2,
p_dist_name IN VARCHAR2,
p_responsible_branch_name IN VARCHAR2,
p_sp_code IN VARCHAR2,
p_sp_name IN VARCHAR2,
p_channel_name IN VARCHAR2,
p_promotion_id IN VARCHAR2,
p_last_name IN VARCHAR2,
p_middle_name IN VARCHAR2,
p_first_name IN VARCHAR2,
p_jobtitle IN VARCHAR2,
p_ldap_username IN VARCHAR2,
p_date_passed IN VARCHAR2,
p_program_name IN VARCHAR2,
p_ryg IN VARCHAR2)
IS
BEGIN
INSERT INTO zmkt7352_promotion_test
(id_promo_rcns,
id_promos,
program_id,
abo_name,
dist_name,
responsible_branch_name,
sp_code,
sp_name,
channel_name,
promotion_id,
last_name,
middle_name,
first_name,
jobtitle,
ldap_username,
date_passed,
program_name,
ryg)
VALUES (
--ZMKT7352_SEC_ID_PROMOTION.nextval,
zmkt7352_sec_id_promotion_test.NEXTVAL,
p_id_promos,
p_program_id,
p_abo_name,
p_dist_name,
p_responsible_branch_name,
p_sp_code,
p_sp_name,
p_channel_name,
p_promotion_id,
p_last_name,
p_middle_name,
p_first_name,
p_jobtitle,
p_ldap_username,
p_date_passed,
p_program_name,
p_ryg );
END insertar_promotion;
PROCEDURE Insertar_header_hetas_import(
p_matriz_name zmkt7352_cap_herramientas_head.matriz_name%TYPE,
p_dealer_dr zmkt7352_cap_herramientas_head.dealer_dr%TYPE,
p_no_revision zmkt7352_cap_herramientas_head.no_revision%TYPE,
p_anio zmkt7352_cap_herramientas_head.anio%TYPE,
p_status_des zmkt7352_cap_herramientas_head.status_des%TYPE,
p_producto_nuevo zmkt7352_cap_herramientas_head.producto_nuevo%TYPE,
p_tipo_producto zmkt7352_cap_herramientas_head.tipo_producto%TYPE,
p_oem zmkt7352_cap_herramientas_head.oem%TYPE,
p_dr zmkt7352_cap_herramientas_head.dr%TYPE,
p_status_gral zmkt7352_cap_herramientas_head.status_gral%TYPE,
p_status_fin zmkt7352_cap_herramientas_head.status_fin%TYPE,
p_rango zmkt7352_cap_herramientas_head.rango%TYPE,
p_tipo_mer zmkt7352_cap_herramientas_head.tipo_mer%TYPE,
v_id OUT zmkt7352_cap_herramientas_head.id_matriz%TYPE)
IS
BEGIN
--begin
/*select ID_MATRIZ into v_id from ZMKT7352_CAP_HERRAMIENTAS_HEAD where
MATRIZ_NAME = p_MATRIZ_NAME and
DEALER_DR = p_DEALER_DR and
NO_REVISION = p_NO_REVISION and
ANIO = p_ANIO and
--STATUS_DES = p_STATUS_DES and
PRODUCTO_NUEVO=p_PRODUCTO_NUEVO and
--OEM=p_OEM and
DR=p_DR and
TIPO_MER=p_TIPO_MER and
--RANGO=p_RANGO and
DELETE_IND='N';*/
SELECT cap_infant_care_head_seq_id.NEXTVAL
INTO v_id
FROM dual;
/*
exception
when NO_DATA_FOUND
then v_id:=0;
end;*/
--
/*
if v_id = 0 then*/
INSERT INTO zmkt7352_cap_herramientas_head
(id_matriz,
matriz_name,
dealer_dr,
no_revision,
anio,
status_des,
producto_nuevo,
tipo_producto,
oem,
dr,
status_gral,
status_fin,
rango,
tipo_mer,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES (
--CAP_INFANT_CARE_HEAD_seq_id.nextval,
v_id,
p_matriz_name,
p_dealer_dr,
p_no_revision,
p_anio,
p_status_des,
p_producto_nuevo,
p_tipo_producto,
p_oem,
p_dr,
p_status_gral,
p_status_fin,
p_rango,
p_tipo_mer,
SYSDATE,
'system',
SYSDATE,
'system',
'N' )
returning id_matriz INTO v_id;
/*
else
update ZMKT7352_CAP_HERRAMIENTAS_HEAD set
MATRIZ_NAME = p_MATRIZ_NAME,
DEALER_DR = p_DEALER_DR,
NO_REVISION = p_NO_REVISION,
ANIO = p_ANIO,
STATUS_DES = p_STATUS_DES,
PRODUCTO_NUEVO=p_PRODUCTO_NUEVO,
OEM=p_OEM,
TIPO_PRODUCTO=p_TIPO_PRODUCTO,
DR=p_DR,
TIPO_MER=p_TIPO_MER,
RANGO=p_RANGO,
LAST_UPDATE_DATE=sysdate,
LAST_UPDATE_BY='system'
where
ID_MATRIZ=v_id
;
end if;*/
END insertar_header_hetas_import;
PROCEDURE Insertar_body_hetas_import(
p_id_matriz zmkt7352_cap_herramientas_body.id_matriz%TYPE,
p_no_parte zmkt7352_cap_herramientas_body.no_parte%TYPE,
p_no_parte_ant zmkt7352_cap_herramientas_body.no_parte_ant%TYPE,
p_descripcion zmkt7352_cap_herramientas_body.descripcion%TYPE,
p_ponderacion zmkt7352_cap_herramientas_body.ponderacion%TYPE,
p_cm zmkt7352_cap_herramientas_body.cm%TYPE,
p_comentarios zmkt7352_cap_herramientas_body.comentarios%TYPE,
p_url zmkt7352_cap_herramientas_body.url%TYPE,
p_precio_dlr zmkt7352_cap_herramientas_body.precio_dlr%TYPE,
p_volumen_vta_1 zmkt7352_cap_herramientas_body.volumen_vta_1%TYPE,
p_volumen_vta_2 zmkt7352_cap_herramientas_body.volumen_vta_2%TYPE)
IS
BEGIN
INSERT INTO zmkt7352_cap_herramientas_body
(id_hmtas_bdy,
id_matriz,
no_parte,
no_parte_ant,
descripcion,
ponderacion,
cm,
comentarios,
url,
precio_dlr,
volumen_vta_1,
volumen_vta_2,
creation_date,
created_by,
last_update_date,
last_update_by,
delete_ind)
VALUES ( cap_infant_care_body_seq_id.NEXTVAL,
p_id_matriz,
p_no_parte,
p_no_parte_ant,
p_descripcion,
p_ponderacion,
p_cm,
p_comentarios,
p_url,
p_precio_dlr,
p_volumen_vta_1,
p_volumen_vta_2,
SYSDATE,
'system',
SYSDATE,
'system',
'N' );
END insertar_body_hetas_import;
PROCEDURE Obtener_emisiones(c_lista OUT SYS_REFCURSOR)
IS
BEGIN
OPEN c_lista FOR
--select SP_CODE, SERVICE_PROVIDER_NAME from ZMKT7352_DR_DEALERS_EBU where SP_CODE = RESPONSIBLE_BRANCH_CODE
SELECT id_emision AS KEY,
emision AS value
FROM zmkt7352_emision;
END obtener_emisiones;
--prueba
PROCEDURE Prueba(p_motor zmkt7352_cap_infant_care_head.id_matriz%TYPE,
id_motor OUT SYS_REFCURSOR)
IS
BEGIN
OPEN id_motor FOR
SELECT id_motores,
motor
FROM zmkt7352_motores
WHERE id_motores = 401;
END prueba;
END zmkt7352_infant_care_pkg;
/
[EDITED by LF: applied [spoiler] tags to visually shorten the message]
[Updated on: Sat, 09 July 2016 16:34] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: PL/SQL error [message #653534 is a reply to message #653532] |
Sat, 09 July 2016 17:55 |
jcasas
Messages: 5
Registered: November 2006
Location: MEXICO
|
Junior Member |
|
|
Thanks Littlefoot, I did highlighted the line error with ***** ERROR IS MARKED IN THIS LINE****** before your reply. I just include only the query where the error appears.
|
|
|
|
|
|
| Re: PL/SQL error [message #653537 is a reply to message #653535] |
Sat, 09 July 2016 20:11 |
jcasas
Messages: 5
Registered: November 2006
Location: MEXICO
|
Junior Member |
|
|
Dear Blacksawn
I did think that too, but I review the structure several times and still do not find which special character is missing, I have this same pkg body in my DEV environment and it is working properly, I do not have a clue why in my staging environment is appearing this error.
Thanks
|
|
|
|
|
|
| Re: PL/SQL error [message #653539 is a reply to message #653537] |
Sat, 09 July 2016 21:09 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
|
The code that you originally posted had a mismatched number of left and right parentheses and various other errors concerning usage of sum and group by and having. The rewrite that you posted fixed those things. The package seems to contain various similar versions of such queries for which ref cursors are opened. You need to make sure you are running what you think you are running and not some prior version. You then need to narrow down the problem by testing things individually. If the problem appears to be in one select statement from one procedure, then run that select statement by itself, outside of the procedure, from SQL*Plus. If you get an error, then start by running just the innermost sub-query by itself. If that runs correctly, then add the next level of sub-query until you determine where the problem is. Sometimes error messages can seem somewhat misleading until you get used to how Oracle looks at things. Frequently, there may be something wrong with the line above or below that causes it to expect something different on the next or preceding line. Another thing that you could try is if your packages or queries are identical and are supposed to be that way, then copy the one that works and overwrite the one that doesn't. If it still doesn't work, then there is something different in the environments. If you can post a describe of each of your tables, then we can use that to test your code. If you can post a copy and paste of an actual run of just the problem select statement outside of the procedure and the resulting error, run from SQL*Plus, so that it shows line numbers and does not mask any errors, then we may also be able to provide further help.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 08:46:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
