jlabuelo,
Not sure if this is what your needing but when I needed to connect the line items back to quotes and accounts I wrote this method to get retrieve all line items in a quote. This method is called in the logic hooks for AOS_Quotes. There are some custom fields in this code that you may note have one specifically is bm_service_id_c.
/**
* Get quotes Product Detail
*/
public function getQuoteLineItems($id)
{
$quoteLineItems = array();
$query = "SELECT " .
" q.`id` AS `quotes_id`, q.`name` AS `quotes_name`, q.`billing_account_id` AS `quotes_billing_account_id`, q.`billing_address_street` AS `quotes_billing_address_street`, q.`billing_address_city` AS `quotes_billing_address_city`, q.`billing_address_state` AS `quotes_billing_address_state`, q.`billing_address_postalcode` AS `quotes_billing_address_postalcode`, q.`billing_address_country` AS `quotes_billing_address_country`, q.`shipping_address_street` AS `quotes_shipping_address_street`, q.`shipping_address_city` AS `quotes_shipping_address_city`, q.`shipping_address_state` AS `quotes_shipping_address_state`, q.`shipping_address_postalcode` AS `quotes_shipping_address_postalcode`, q.`shipping_address_country` AS `quotes_shipping_address_country`, q.`expiration` AS `quotes_expiration`, q.`number` AS `quotes_number`, q.`opportunity_id` as `quotes_opportunity_id`, q.`total_amt` AS `quotes_total_amt`, q.`total_amt_usdollar` AS `quotes_total_amt_usdollar`, q.`subtotal_amount` AS `quotes_subtotal_amount`, q.`subtotal_amount_usdollar` AS `quotes_subtotal_amount_usdollar`, q.`discount_amount` AS `quotes_discount_amount`, q.`discount_amount_usdollar` AS `quotes_discount_amount_usdollar`, q.`tax_amount` AS `quotes_tax_amount`, q.`tax_amount_usdollar` AS `quotes_tax_amount_usdollar`, q.`shipping_amount` AS `quotes_shipping_amount`, q.`shipping_amount_usdollar` AS `quotes_shipping_amount_usdollar`, q.`shipping_tax` AS `quotes_shipping_tax`, q.`shipping_tax_amt` AS `quotes_shipping_tax_amt`, q.`shipping_tax_amt_usdollar` AS `quotes_shipping_tax_amt_usdollar`, q.`total_amount` AS `quotes_total_amount`, q.`total_amount_usdollar` AS `quotes_total_amount_usdollar`, q.`currency_id` AS `quotes_currency_id`, q.`stage` AS `quotes_stage`, q.`term` AS `quotes_term`, q.`terms_c` AS `quotes_terms_c`, q.`approval_status` AS `quotes_approval_status`, q.`invoice_status` AS `quotes_invoice_status`, q.`subtotal_tax_amount` AS `quotes_subtotal_tax_amount`, q.`subtotal_tax_amount_usdollar` AS `quotes_subtotal_tax_amount_usdollar`," .
" pq.`id` AS `product_quotes_id`, pq.`name` AS `product_quotes_name`, pq.`part_number` AS `product_quotes_part_number`, pq.`item_description` AS `product_quotes_item_description`, pq.`number` AS `product_quotes_number`, pq.`product_qty` AS `product_quotes_product_qty`, pq.`product_cost_price` AS `product_quotes_product_cost_price`, pq.`product_cost_price_usdollar` AS `product_quotes_product_cost_price_usdollar`, pq.`product_list_price` AS `product_quotes_product_list_price`, pq.`product_list_price_usdollar` AS `product_quotes_product_list_price_usdollar`, pq.`product_discount` AS `product_quotes_product_discount`, pq.`product_discount_usdollar` AS `product_quotes_product_discount_usdollar`, pq.`product_discount_amount` AS `product_quotes_product_discount_amount`, pq.`product_discount_amount_usdollar` AS `product_quotes_product_discount_amount_usdollar`, pq.`discount` AS `product_quotes_discount`, pq.`product_unit_price` AS `product_quotes_product_unit_price`, pq.`product_unit_price_usdollar` AS `product_quotes_product_unit_price_usdollar`, pq.`vat_amt` AS `product_quotes_vat_amt`, pq.`vat_amt_usdollar` AS `product_quotes_vat_amt_usdollar`, pq.`product_total_price` AS `product_quotes_product_total_price`, pq.`product_total_price_usdollar` AS `product_quotes_product_total_price_usdollar`, pq.`vat` AS `product_quotes_vat`, pq.`parent_type` AS `product_quotes_parent_type`, pq.`parent_id` AS `product_quotes_parent_id`, pq.`product_id` AS `product_quotes_product_id`, pq.`group_id` AS `product_quotes_group_id`," .
" p.`id` AS product_id, p.`name` AS `product_name`, p.`part_number` AS `product_part_number`, p.`category` AS `product_category`, p.`type` AS `product_type`, p.`cost` AS `product_cost`, p.`cost_usdollar` AS `product_cost_usdollar`, p.`currency_id` AS `product_currency_id`, p.`price` AS `product_price`, p.`price_usdollar` AS `product_price_usdollar`, p.`aos_product_category_id` AS `product_aos_product_category_id`," .
" c.`bm_serviceid_c`," .
" a.`id` AS `account_id`, a.`name` AS `account_name`, a.`description` AS `account_description`, a.`phone_fax` AS `account_phone_fax`, a.`billing_address_street` AS `account_billing_address_street`, a.`billing_address_city` AS `account_billing_address_city`, a.`billing_address_state` AS `account_billing_address_state`, a.`billing_address_postalcode` AS `account_billing_addess_postalcode`, a.`billing_address_country` AS `account_billing_address_country`, a.`rating` AS `account_rating`, a.`phone_office` AS `account_phone_office`, a.`phone_alternate` AS `account_phone_alternate`, a.`website` AS `account_website`, a.`ownership` AS `account_ownership`, a.`employees` AS `account_employees`, a.`shipping_address_street` AS `account_shipping_address_street`, a.`shipping_address_city` AS `account_shipping_address_city`, a.`shipping_address_state` AS `account_shipping_address_state`, a.`shipping_address_postalcode` AS `account_shipping_address_postalcode`, a.`shipping_address_country` AS `account_shipping_address_country`, a.`parent_id` AS `account_parent_id`, a.`sic_code` AS `account_sic_code`, a.`campaign_id` AS `account_campaign_id`," .
" ac.`invday_c` AS `account_invday_c`" .
" FROM aos_quotes q" .
" LEFT JOIN aos_products_quotes pq ON pq.parent_id=q.id" .
" LEFT JOIN aos_products p ON p.id=pq.product_id" .
" LEFT JOIN aos_products_quotes_cstm c ON c.id_c=pq.id" .
" LEFT JOIN accounts a ON a.id=q.billing_account_id" .
" LEFT JOIN accounts_cstm ac ON ac.id_c=a.id" .
" WHERE q.id='{$id}'";
$result = $GLOBALS['db']->query($query);
while ($row = $GLOBALS['db']->fetchByAssoc($result)) {
$quoteLineItems[] = $row;
}
return $quoteLineItems;
}
Hope this helps.
Tony